What's the difference between the following SELECT statements?
SELECT * FROM tableA, tableB
WHERE tableA.a1 = tableB.b1
and
SELECT * FROM tableA JOIN tableB
ON tableA.a1 = tableB.b1
Someone told me the first statement isn't ANSI standard, but a lot of my existing code uses that syntax. Do I need to change the non-ANSI standard SELECT statements? Does one format perform better than the other?
Many people incorrectly think that ANSI doesn't support the ability to define a JOIN condition in a query's WHERE predicate. In truth, ANSI lets you define a JOIN condition in either a query's WHERE clause or its JOIN clause. A similar question recently came up on the public newsgroups, and I liked SQL Server MVP Steve Kass's advice and comments. Both SELECT statements are standard ANSI syntax, though not everyone recognizes that. Neither ANSI nor Microsoft favors one statement syntax over the other. The only JOIN syntax ANSI discourages is the old standard of specifying an outer join by using an asterisk and equals symbol (*=). Because ANSI SQL-89 didn't support outer joins (ANSI SQL-92 introduced that support), vendors had to devise their own syntax. But today, ANSI SQL-92 requires you to specify an outer join in the FROM clause. In fact, SQL Server will produce an incorrect answer if you use the old outer join syntax and include an IS NULL predicate in a column on the dependent side of the join.
Both inner join queries in your question are well defined; the choice between them is largely a matter of taste. Many people prefer the second format because the consequences of accidentally issuing SELECT * FROM A JOIN B and forgetting the ON clause is less disastrous than issuing SELECT * FROM A, B and forgetting the WHERE clause. If you forget the ON clause in the second query, the query won't run. But if you forget the WHERE clause in the first query, that query could produce dramatically wrong results and could consume a huge amount of resources if the query produces a Cartesian product between large tables. I find it easier to read queries that specify JOIN conditions in the JOIN clause. Although performance of both query formats is usually identical, sometimes you'll get different plans in complicated join situations if you specify the condition in either the WHERE or FROM clause.