INNER JOIN ON vs WHERE clause

by

INNER JOIN is ANSI syntax that you should use.

It is generally considered more readable, especially when you join lots of tables.

It can also be easily replaced with an OUTER JOIN whenever a need arises.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It’s easier to see this with the WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also, note that MySQL also has a STRAIGHT_JOIN clause.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

You cannot control this in MySQL using WHERE syntax.

Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. Difference between JOIN and INNER JOIN
  3. Join vs. sub-query
  4. How can I do a FULL OUTER JOIN in MySQL?
  5. SQL Inner-join with 3 tables?
  6. Subqueries vs joins
  7. MySQL – Selecting data from multiple tables all with same structure but different data
  8. INNER JOIN vs INNER JOIN (SELECT . FROM)
  9. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  10. MySQL: GROUP_CONCAT with LEFT JOIN
  11. Multiple INNER JOIN SQL ACCESS
  12. Performing Inner Join for Multiple Columns in the Same Table
  13. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  14. Best way to test if a row exists in a MySQL table
  15. MySQL LIKE IN()?
  16. Can table columns with a Foreign Key be NULL?
  17. Adding a new SQL column with a default value
  18. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  19. Delete sql rows where IDs do not have a match from another table
  20. SQL join on multiple columns in same tables
  21. How can I join multiple SQL tables using the IDs?
  22. Join between tables in two different databases?
  23. update columns values with column of another table based on condition [duplicate]
  24. MySQL: Transactions vs Locking Tables
  25. mysql – how many columns is too many?
  26. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  27. Comparing date ranges
  28. 1052: Column ‘id’ in field list is ambiguous
  29. How to use mysql JOIN without ON condition?
  30. MySQL syntax for Join Update
  31. SQLite – How do you join tables from different databases?
  32. Get all characters before space in MySQL
  33. How do I rename an Index in MySQL
  34. How to do join on multiple criteria, returning all combinations of both criteria?
  35. Can you define “literal” tables in SQL?
  36. MySQL: Compare differences between two tables
  37. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  38. PostgreSQL GROUP BY different from MySQL?
  39. MySQL LEFT JOIN 3 tables
  40. how to use LIKE with column name
  41. What is the difference between a hash join and a merge join (Oracle RDBMS )?
  42. Including null values in an Apache Spark Join
  43. Getting the number of rows with a GROUP BY query
  44. Why do multiple-table joins produce duplicate rows?
  45. MySQL select rows where left join is null
  46. MySQL Trigger to prevent INSERT under certain conditions
  47. How to group mysql rows with same column value into one row?
  48. MySql difference between two timestamps in Seconds?
  49. A simple way to sum a result from UNION in MySQL
  50. SQL Inner Join On Null Values

Leave a Comment