In MySQL queries, why use join instead of where?

by

Any query involving more than one table requires some form of association to link the results from table “A” to table “B”. The traditional (ANSI-89) means of doing this is to:

  1. List the tables involved in a comma separated list in the FROM clause

  2. Write the association between the tables in the WHERE clause

    SELECT *
  FROM TABLE_A a,
       TABLE_B b
 WHERE a.id = b.id

Here’s the query re-written using ANSI-92 JOIN syntax:

SELECT *
  FROM TABLE_A a
  JOIN TABLE_B b ON b.id = a.id

From a Performance Perspective:

Where supported (Oracle 9i+, PostgreSQL 7.2+, MySQL 3.23+, SQL Server 2000+), there is no performance benefit to using either syntax over the other. The optimizer sees them as the same query. But more complex queries can benefit from using ANSI-92 syntax:

  • Ability to control JOIN order – the order which tables are scanned
  • Ability to apply filter criteria on a table prior to joining

From a Maintenance Perspective:

There are numerous reasons to use ANSI-92 JOIN syntax over ANSI-89:

  • More readable, as the JOIN criteria is separate from the WHERE clause
  • Less likely to miss JOIN criteria
  • Consistent syntax support for JOIN types other than INNER, making queries easy to use on other databases
  • WHERE clause only serves as filtration of the cartesian product of the tables joined

From a Design Perspective:

ANSI-92 JOIN syntax is pattern, not anti-pattern:

  • The purpose of the query is more obvious; the columns used by the application is clear
  • It follows the modularity rule about using strict typing whenever possible. Explicit is almost universally better.

Conclusion

Short of familiarity and/or comfort, I don’t see any benefit to continuing to use the ANSI-89 WHERE clause instead of the ANSI-92 JOIN syntax. Some might complain that ANSI-92 syntax is more verbose, but that’s what makes it explicit. The more explicit, the easier it is to understand and maintain.

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  3. Can table columns with a Foreign Key be NULL?
  4. MySQL not using indexes with WHERE IN clause?
  5. MySQL dump by query
  6. single fixed table with multiple columns vs flexible abstract tables
  7. How many rows in a database are TOO MANY?
  8. SQLite – ORDER BY RAND()
  9. SQL – How to transpose?
  10. Which SQL query is better, MATCH AGAINST or LIKE?
  11. MySQL equivalent of DECODE function in Oracle
  12. How to delete Duplicates in MySQL table
  13. Best database field type for a URL
  14. Query that ignore the spaces
  15. How to Optimize Queries in a Database – The Basics
  16. MySQL – how to front pad zip code with “0”?
  17. Create date from day, month, year fields in MySQL
  18. Find rows that have the same value on a column in MySQL
  19. what is the difference between GROUP BY and ORDER BY in sql
  20. How do I list all the columns in a table?
  21. Two single-column indexes vs one two-column index in MySQL?
  22. VARCHAR as foreign key/primary key in database good or bad?
  23. How to find all IDs of children recursively?
  24. Natural Sort in MySQL
  25. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  26. Best way to check for “empty or null value”
  27. When or why would you use a right outer join instead of left?
  28. What are the benefits of using database cursor?
  29. What is semi-join in database?
  30. What does collation mean?

Leave a Comment