SQL JOIN Syntax in MS SQL

Question

I have been taught in my MSSQL classes, this is how to join two tables

select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID

Now in my professional life, I came across JOIN queries like this

select * from FirstTable A, SecondTable B
where A.ID=B.ID

I know the second option was once norm but perhaps now abandoned.

I find that in complex queries where I join 6+ tables + have a number of sub queries , the second form is a lot easier to understand and is short and pretty.

Questions

• Which one should I use?
• Is there an advantage of one over the other?

Answer 1

Queries of the second type fall under what some call a SQL antipattern (check out the nice book written by Bill Karwin). The second query almost resembles a Cartesian JOIN whose WHERE clause has to be evaluated on the fly.

The first is cleaner and the order of execution can be better managed.

You could compare both ways by

• getting the EXPLAIN plan for each and seeing if execution time is the same
• creating a third type that refactors the query and get its EXPLAIN plan and running time
• see if all indexes are being evaulated the same for the three query styles

You are better off going for the JOIN syntax because you can generate result sets with LEFT JOINs and RIGHT JOINs that may be far different from (maybe more desirable than) INNER JOINs.

Answer 2

This SO posting has a good explanation of the differences in ANSI SQL complaince.

While both queries will produce the same result, I find that it is always a good idea to explicitly state your JOINs. It's much easier to understand, especially in queries that contain non-JOIN-related evaluations in the WHERE clause.

Explicitly stating your JOINs also saves you from inadvertently producing a Cartesian product (as RolandoMySQLSBA alluded to). In your 2nd query above, if you (for whatever reason) forgot to include your WHERE clause, your query would run without JOIN conditions and return a result set of every row in FirstTable matched with every row in SecondTable. In addition to not returning what you want, if you make a mistake like that with large tables (with hundreds of thousands or even millions of rows) you can cause some performance issues while the database attempts to fulfill that query.

Answer 3

You want to use the first syntax. The second syntax is deprecated and in some cases (strange outer join situations) can work incorrectly.