4

Possible Duplicate:
INNER JOIN versus WHERE clause — any difference?
SQL JOIN: is there a difference between USING, ON or WHERE?

For example, I have this SQL statement:

SELECT *  
  FROM orders, inventory  
 WHERE orders.product = inventory.product

or

SELECT *  
  FROM orders  
  JOIN inventory  
    ON orders.product = inventory.product

What is the difference between these two?

Community
  • 1
  • 1
volk
  • 1,196
  • 1
  • 12
  • 31
  • 1
    Using `,` shows someone that follows blindly - Someone who copied what they were shown, but didn't develop themselves enough to find out why they shouldn't be using it. KUDOS for asking :) – MatBailie Oct 26 '11 at 19:09

4 Answers4

13

They do exactly the same thing, but I'd recommend the second approach for readability and maintainability.

  1. Using JOIN allows you to separate the conditions that define relationships between tables from conditions which are filters on the result set.

  2. Using JOIN makes it easier to see if you are missing a join condition.

  3. Using JOIN allows you to easily choose between INNER or OUTER JOIN. The comma syntax is equivalent to INNER JOIN (though some databases do have an extension to allow an outer join when using the first approach).

  4. The most important is to be consistent about which you use. The comma syntax has different precedence from the JOIN keyword which can lead to confusing errors if you try to mix the two syntaxes in the same query. Because of point 3, it is easier to be consistent if you always use JOIN.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    Points 3 and 4 are important. Implied JOINs (in WHERE) are easler to bollix up. http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338 – gbn Oct 26 '11 at 18:38
  • 2
    Implied joins area SQL antipattern, there is no excuse for ever using one. – HLGEM Oct 26 '11 at 18:41
3

Inner join is ansi syntax, should be the preferred method.

Imagine how ugly this solution could get if you were to use the , with say many tables? SELECT * FROM orders, inventory, products, logistics, accounting, materials, ...

Be kind to your future developers and anyone else looking at or maintaining this code use the JOIN syntax.

JonH
  • 32,732
  • 12
  • 87
  • 145
  • 1
    Both are ANSI syntax. Explicit JOIN was added with SQL92. The implicit JOIN is SQL89 – gbn Oct 26 '11 at 18:35
2

The comma (,) is equivalent to an CROSS JOIN. Using an explicit CROSS JOIN is more intuitive and recommended, as it can easily be changed to a LEFT JOIN, RIGHT JOIN, etc. Using CROSS JOIN is also ANSI-compliant.

  • -1 - The comma is equivalent to a `CROSS JOIN`, but you make it an `INNER JOIN` by the filter in the `WHERE` clause. – JNK Oct 26 '11 at 19:00
  • @JNK thanks for the correction, I have edited to reflect. –  Oct 26 '11 at 19:24
-1

They are functionally equivalent. The second one is 'newer'.

A.H.
  • 63,967
  • 15
  • 92
  • 126
Randy
  • 16,480
  • 1
  • 37
  • 55
  • 1
    The second is a lot more than "newer" (and by newer we mean it came out almost 20 years ago), it is also less prone to error and far easier to maintain over time especially as things get complicated. The first is syntax that should never be used. – HLGEM Oct 26 '11 at 18:43
  • both were introduced within 4 years of each other - about 20 years ago... they are functionally equivalent. you can get off your high horse at any time. – Randy Oct 26 '11 at 21:24