If one writes query Q in a relational DBMS, won't the optimizer choose the best way to execute it (depending on multiple factors) no matter how one formulates Q? I'm curious about SQL Server and Oracle.

For instance, let Q be:

```
SELECT * 
FROM t1, t2
WHERE t1.some_column = t2.some_column

```

Provided the right indexes (with the right selectivity) exist, we should see index seeks followed maybe by key lookups. What we won't see is a cross product followed by a selection in the execution plan.

Then why does <https://technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx> state that "In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. " No matter how you write a query Q and no matter the query class of Q (SPJ, SPJ + UNION, SPJ + subqueries, etc), won't the optimizer find the best semantically equivalent version?

Thank you!