sql - Whether to use JOIN or not?

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?

Answer

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.