It seems like to combine two or more tables, we can either use join or where. What are the advantages of one over the other?
“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables.
When an inner join is used there is no difference between On and Where clauses. You get the same result from both. But with left joins you do get the difference between On and Where in SQL Server.
Joins are a more static way to combine data. Joins must be defined between physical tables up front, before analysis, and can't be changed without impacting all sheets using that data source. Joined tables are always merged into a single table.
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:
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
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:
There are numerous reasons to use ANSI-92 JOIN syntax over ANSI-89:
ANSI-92 JOIN syntax is pattern, not anti-pattern:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With