In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?

by

The ON clause defines the relationship between the tables.

The WHERE clause describes which rows you are interested in.

Many times you can swap them and still get the same result, however this is not always the case with a left outer join.

  • If the ON clause fails you still get a row with columns from the
    left table but with nulls in the columns from the right table.
  • If the WHERE clause fails you won’t get that row at all.

More Related Contents:

  1. How can I do a FULL OUTER JOIN in MySQL?
  2. INNER JOIN ON vs WHERE clause
  3. Join vs. sub-query
  4. SQL JOIN – WHERE clause vs. ON clause
  5. Subqueries vs joins
  6. Filter Table Before Applying Left Join
  7. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  8. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  9. MySQL – Selecting data from multiple tables all with same structure but different data
  10. Is a JOIN faster than a WHERE?
  11. MySQL: GROUP_CONCAT with LEFT JOIN
  12. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  13. How to display data in JOIN, and return null value? [closed]
  14. Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
  15. Generating a range of numbers in MySQL
  16. How to get next/previous record in MySQL?
  17. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  18. Error 1046 No database Selected, how to resolve?
  19. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  20. mysql, iterate through column names
  21. Best database field type for a URL
  22. Difference between these two joining table approaches?
  23. MySQL – how to front pad zip code with “0”?
  24. Find the number of columns in a table
  25. Mysql, reshape data from long / tall to wide
  26. Mysql strip time component from datetime
  27. How do I start auto increment from a specific point?
  28. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  29. In what order are MySQL JOINs evaluated?
  30. MySql bulk load command line tool

Leave a Comment