Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 for "anti pattern" –  gbn Apr 19 '12 at 8:46
    
Never heard about explain plan. I must say though for the query that I am working on, form b looks better and a lot easier to follow. I have 10 joins which includes subqueries. All my where clauses are JOINS. –  Jackofall Apr 19 '12 at 17:15
    
@Jackofall the equivalent of "explain plan" in SQL Server is "show execution plan." You can turn this on for any query using the "Include actual execution plan" button on the toolbar in SSMS. –  Aaron Bertrand Apr 23 '12 at 4:12

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.

share|improve this answer
    
+1 for pointing out the ease-of-understanding of explicitly stating joins. If any other developers looked at the 2 different queries, it's much simpler to know expected results with the explicit joins. –  Derek Downey Apr 19 '12 at 13:31

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

share|improve this answer
1  
Second syntax isn't deprecated (just *= / =*), though I agree that the first is preferable for a variety of reasons. –  Aaron Bertrand Apr 18 '12 at 22:46
    
How can the second syntax work - at all - in outer joins? –  ypercube Apr 18 '12 at 22:46
    
@ypercube *= / =* was once part of the SQL standard and those signified outer joins - also it can become a cross join if you leave out the where clause (tougher to do with explicit join syntax). –  Aaron Bertrand Apr 18 '12 at 22:48
    
Oh, I thought that was used only in SQL-Server and Sybase. I din't know it was in standard SQL. –  ypercube Apr 18 '12 at 22:52
1  
I agree 100% ... if you are using *= / =* today, you're doing it wrong. Plenty of wrong results / unpredictable results bugs, which is why the syntax was deprecated in favor of explicit outer join syntax. I just wanted to make it clear that FROM a, b, c is not deprecated for inner / cross joins. –  Aaron Bertrand Apr 18 '12 at 23:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.