SQL INNER JOIN syntax

by

Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it’s in the older definitions, just wasn’t particularly widely used before then).

The use of the JOIN syntax is strongly preferred as it separates the join logic from the filtering logic in the WHERE clause. Whilst the JOIN syntax is really syntactic sugar for inner joins it’s strength lies with outer joins where the old * syntax can produce situations where it is impossible to unambiguously describe the join and the interpretation is implementation-dependent. The [LEFT | RIGHT] JOIN syntax avoids these pitfalls, and hence for consistency the use of the JOIN clause is preferable in all circumstances.

Note that neither of these two examples are Cartesian products. For that you’d use either

SELECT c.name, o.product  
FROM customer c, order o  
WHERE o.value = 150

or

SELECT c.name, o.product  
FROM customer c  CROSS JOIN order o 
WHERE o.value = 150

More Related Contents:

  1. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  2. INNER JOIN ON vs WHERE clause
  3. Update statement with inner join on Oracle
  4. Is it possible to insert multiple rows at a time in an SQLite database?
  5. CROSS JOIN vs INNER JOIN in SQL
  6. SQL JOIN: is there a difference between USING, ON or WHERE?
  7. Difference between JOIN and INNER JOIN
  8. How can I delete using INNER JOIN with SQL Server?
  9. SQL Inner-join with 3 tables?
  10. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  11. Difference between LIKE and ~ in Postgres
  12. Multiple INNER JOIN SQL ACCESS
  13. Is there a shortcut for SELECT * FROM?
  14. SQL left join vs multiple tables on FROM line?
  15. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  16. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  17. Unrecognized name: employees at [9:8]
  18. WHERE Clause vs ON when using JOIN
  19. Mixing implicit and explicit JOINs
  20. What is easier to read in EXISTS subqueries? [closed]
  21. How do I perform a GROUP BY on an aliased column in SQL Server?
  22. Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?
  23. Eliminating duplicate values based on only one column of the table
  24. SQL dot notation
  25. Curly braces in T-SQL
  26. Syntax of for-loop in SQL Server
  27. SQL Inner join more than two tables
  28. combinations (not permutations) from cross join in sql
  29. How do I perform update query with subquery in Access?
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment