Difference between these two joining table approaches?

by

Other than syntax, for the small snippet, they work exactly the same. But if at all possible, always write new queries using ANSI-JOINs.

As for semantically, the comma notation is used to produce a CARTESIAN product between two tables, which means produce a matrix of all records from table A with all records from table B, so two tables with 4 and 6 records respectively produces 24 records. Using the WHERE clause, you can then pick the rows you actually want from this cartesian product. However, MySQL doesn’t actually follow through and make this huge matrix, but semantically this is what it means.

A JOIN syntax is the ANSI standard that more clearly defines how tables interact. By putting the ON clause next to the JOIN, it makes it clear what links the two tables together.

Functionally, they will perform the same for your two queries. The difference comes in when you start using other [OUTER] JOIN types.

For MySQL specifically, comma-notation does have one difference

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

However, it would not be wise to bank on this difference.

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. 1052: Column ‘id’ in field list is ambiguous
  3. Join tables with SUM issue in MYSQL
  4. Join between tables in two different databases?
  5. Required to join 2 tables with their FKs in a 3rd table
  6. How can I join multiple SQL tables using the IDs?
  7. Why does MySQL report a syntax error on FULL OUTER JOIN?
  8. Mixing ANSI 1992 JOINs and COMMAs in a query
  9. Cumulative sum over a set of rows in mysql
  10. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  11. MySQL JOIN the most recent row only?
  12. How to find non-existing data from another Table by JOIN?
  13. MySQL Inner Join Query Multiple Tables
  14. Laravel join with 3 Tables
  15. MySQL LEFT JOIN 3 tables
  16. how to use a like with a join in sql?
  17. When to use a left outer join?
  18. In what order are MySQL JOINs evaluated?
  19. How to store a one to many relation in MySQL database?
  20. MySQL Multiple Joins in one query?
  21. MYSQL OR vs IN performance
  22. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  23. MySQL JOIN ON vs USING?
  24. MySQL: View with Subquery in the FROM Clause Limitation
  25. MySql export schema without data
  26. limiting the rows to where the sum a column equals a certain value in MySQL
  27. MySQL Pivot Table Column Data as Rows
  28. updating columns with a sequence number mysql
  29. MySQL, Concatenate two columns
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment