Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?

by

Filtering joins solely using WHERE can be extremely inefficient in some common scenarios. For example:

SELECT * FROM people p, companies c 
    WHERE p.companyID = c.id AND p.firstName="Daniel"

Most databases will execute this query quite literally, first taking the Cartesian product of the people and companies tables and then filtering by those which have matching companyID and id fields. While the fully-unconstrained product does not exist anywhere but in memory and then only for a moment, its calculation does take some time.

A better approach is to group the constraints with the JOINs where relevant. This is not only subjectively easier to read but also far more efficient. Thusly:

SELECT * FROM people p JOIN companies c ON p.companyID = c.id
    WHERE p.firstName="Daniel"

It’s a little longer, but the database is able to look at the ON clause and use it to compute the fully-constrained JOIN directly, rather than starting with everything and then limiting down. This is faster to compute (especially with large data sets and/or many-table joins) and requires less memory.

I change every query I see which uses the “comma JOIN” syntax. In my opinion, the only purpose for its existence is conciseness. Considering the performance impact, I don’t think this is a compelling reason.

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. Subqueries vs joins
  5. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  6. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  7. MySQL – Selecting data from multiple tables all with same structure but different data
  8. MySQL: GROUP_CONCAT with LEFT JOIN
  9. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  10. Explicit vs implicit SQL joins
  11. 1052: Column ‘id’ in field list is ambiguous
  12. MySQL Insert query doesn’t work with WHERE clause
  13. Delete from two tables in one query
  14. Left Outer Join Not Working?
  15. Fulltext Search with InnoDB
  16. What’s the best way to join on the same table twice?
  17. MySQL dump by query
  18. Delete sql rows where IDs do not have a match from another table
  19. What does DELIMITER // do in a Trigger?
  20. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  21. MySQL equivalent of DECODE function in Oracle
  22. How do you version your database schema? [closed]
  23. MySQL, Get users rank
  24. Mysql: how to select groups having certain values?
  25. Boolean vs tinyint(1) for boolean values in MySQL
  26. Venn Diagram for Natural Join
  27. Why do multiple-table joins produce duplicate rows?
  28. Find rows that have the same value on a column in MySQL
  29. what is the difference between GROUP BY and ORDER BY in sql
  30. IN vs. JOIN with large rowsets

Leave a Comment