Menu
I know we can do correlated subqueries and join. But which one is faster? Is there a golden rule or I must measure both?
626
If you need to combine related information from different rows within a table, then you can join the table with itself. Use subqueries when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. Because the subquery may be evaluated once for each row processed by the outer query, it can be slow.
CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
“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.
First, a correlated subquery really is a type of join. There is no golden rule about which produces the best execution plan. If you are interested in performance, you need to try out the different forms to see what works best. Or, at least, look at the exeuction plans to make that decision.
In general, I tend to avoid correlated subqueries for a couple of reasons. First, they can almost always be written without the correlation. Second, many query engines turn them into nested loop joins (albeit using indexes), and other join strategies might be better. In such cases, correlated subqueries make it difficult to parallelize the query. Third, correlated subqueries are usualy in either the SELECT or WHERE clauses. I like for all my tables to be in the FROM clause.
In MySQL however, correlated subqueries are often the most efficient way to do a query. This is especially true when using a subquery in an IN clause. So, there is no golden rule.
74
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
