Is a join optimized to a where clause at runtime?

When I write a query like this...

select *
from table1 t1
join table2 t2
on t1.id = t2.id

Does the SQL optimizer, not sure if that is the correct term, translate that to...

select *
from table1 t1, table2 t2
where t1.id = t2.id

Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?

Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.

Those will usually collapse to the same thing internally. The former is the one you should always write, IMHO. More importantly, why does it matter? They're identical in terms of execution plan and performance (assuming you don't mess it up, which is easier to do with the lazy, old-style syntax).

Here is proof using AdventureWorks that there is no CROSS JOIN and filter going on.

The explicit join:



The implicit join:



Look, ma! Identical plans, identical results, no cross joins or filters seen anywhere.

(For clarity, the warning on the SELECT operator in both cases is a cardinality-affecting implicit convert, nothing to do with the join in either case.)

StackExchange Database Administrators Q#35596, answer score: 21