SQL JOIN – WHERE clause vs. ON clause

by

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

More Related Contents:

  1. Filter Table Before Applying Left Join
  2. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  3. Is a JOIN faster than a WHERE?
  4. What’s the difference between “where” clause and “on” clause when table left join?
  5. Explicit vs implicit SQL joins
  6. SQL JOIN and different types of JOINs
  7. SQL update from one Table to another based on a ID match
  8. SQL JOIN: is there a difference between USING, ON or WHERE?
  9. Left Join With Where Clause
  10. SQL Inner-join with 3 tables?
  11. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  12. What is the difference between HAVING and WHERE in SQL?
  13. SQL left join vs multiple tables on FROM line?
  14. SQL Joins Vs SQL Subqueries (Performance)?
  15. FULL OUTER JOIN with SQLite
  16. SQLite – How do you join tables from different databases?
  17. What’s the best way to join on the same table twice?
  18. Can we use join for two different database tables?
  19. Call a set-returning function with an array argument multiple times
  20. MySQL – Selecting data from multiple tables all with same structure but different data
  21. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  22. ORA – 00933 confusion with inner join and “as”
  23. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  24. Venn Diagram for Natural Join
  25. SQL query: Simulating an “AND” over several rows instead of sub-querying
  26. Why do multiple-table joins produce duplicate rows?
  27. SQL SELECT from multiple tables
  28. sql query if parameter is null select all
  29. IN vs. JOIN with large rowsets
  30. SQL join on multiple columns in same tables

Leave a Comment