SQL JOIN Syntax in MS SQL Server 2008

join;sql-server-2008

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?

Best Answer

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.

Related Question