Simple SQL Join Understanding?

The second version, with the explicit JOIN and join condition is standardized SQL.

The implicit join syntax with a WHERE clause is deprecated syntax (or, rather, considered bad) - partially because it is easy to forget the WHERE clause and cause a Cartesian product.

Why Use the new syntax?

As others have stated, the new syntax has become the preferred convention. In larger queries the new syntax is easier to read, debug, and ensure the join criteria is added (meaning no accidental CROSS JOINS.

Is the old syntax deprecated (for inner joins)?

Not according to ANSI -- both are valid, even if the first is disfavored. Although, performing outer joins in the old syntax has been deprecated -- mainly because it can be ambiguous.

How consensus is the "use the new syntax" view ?

• Aaron Bertran considers it a "bad habit to kick"
• SQL Server Central the consensus was "Kill kill kill it with fire"
• Joe Celko -- meh, but it's easier to read.

Both will output the same and are just different variations of writing the query.

SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t2.id = t1.id

Is the preferred join method as you are explicitly stating which type of join you are using, i.e. LEFT, OUTER, INNER.