Menu
I've been looking far and wide, but I can't find an answer, probably because I can't figure out the right way to ask the question. So here it is: is there any reason to prefer any of these two queries?
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
and
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
The question stems from a much more complicated query that I am trying to optimize, but I think those two queries contain the essence of the question, and hopefully by asking in this way it will be more useful to others. Thanks in advance.
862
The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.
“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.
Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow. There's an example of this in the subqueries lesson. Aggregations: Combining multiple rows to produce a result requires more computation than simply retrieving those rows.
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
and
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
will return the same results. However you should prefer the second one in this case. The first form is still widely encountered because Oracle did not support the second form for a long time.
Yet, stating INNER JOIN carries intent and forces you to write a condition because an INNER JOIN requires an ON clause. For larger queries it makes the query far more readable and makes it harder to skip a joining predicate.
Also, note that I would tend to read the first form as: take the cartesian product of the two tables, and only retain rows that have equal ids in both tables which in SQL is expressed as SELECT * FROM table1 CROSS JOIN table2 WHERE table1.id = table2.id;.
148
Actually the two queries are the same, only two different ways to write it. Personally I think that the latter one gives a little more readability for the user.
33
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
