http://www.sqlservercentral.com/blogs/brian_kelley/2009/09/30/the-old-inner-join-syntax-vs-the-new-inner-join-syntax/

Printed 2014/11/26 08:32AM

The old INNER JOIN syntax vs. the new INNER JOIN syntax

2009/09/30

I was in a presentation last week where the presenter was talking about the ANSI/ISO SQL standards. For the most part the information was correct, but one of the things that was left ambiguous was whether or not the old INNER JOIN syntax was valid (and thus, the audience would be left to assume that it wasn't):

SELECT C.FirstNameC.LastName E.Title
FROM Person.Contact C
HumanResources.Employee E
WHERE C.ContactID 
E.ContactID
  
AND C.LastName 
'Smith'
ORDER BY LastNameFirstName;
    

The old syntax is where the tables are specified in the FROM clause but the JOIN clause is specified in the WHERE clause, like the query above (WHERE C.ContactID = E.ContactID). It is valid ANSI/ISO SQL, and you can find a bit of confirmation for this in Books Online. The reason it's valid is because it's not ambiguous, unlike the OUTER JOIN syntax that was previously used. Now most DBAs and DB developers that I know much prefer the new syntax where the JOIN clause is specified explicitly in the FROM clause using the ON operator like so:

SELECT C.FirstNameC.LastNameE.Title
FROM 
Person.Contact C
  
INNER JOIN 
HumanResources.Employee E
    ON C.ContactID 
E.ContactID
WHERE C.LastName 
'Smith'
ORDER BY LastNameFirstName;
  

The reason I prefer the new syntax is it's cleaner and easier to read, at least IMHO. I can immediately tell there's an INNER JOIN operation going on and I can clearly see the JOIN clause. When the JOIN clause is in the WHERE clause, I have to sift through that portion to determine which exactly is the JOIN clause. On the first query I've given above, that's not terribly difficult, but when the WHERE clause has a lot more to it, it can be a chore to piece together the query properly.

When I queried on Twitter to see who was using the old syntax, I got back a lot of "Kill it! Kill it!" type of responses like someone had just seen a cockroach headed for the 2 year-old's birthday cake. Others pointed out that they see it, but in legacy code, because there's a standing ban on the old syntax for new code. A few acknowledged that it still goes on in their shops (and they were usually quick to point out that it drove them nuts). I'm interested in seeing if there's anyone that still codes using the old syntax and if you do, is there a reason you haven't moved on to the new syntax? Is it because it's an ingrained habit? Or maybe that way is more clear to you because it's what you're used to? Another reason?

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.