Join vs. sub-query

sql mysql subquery join
Your Common Sense picture Your Common Sense · Apr 5, 2010 · Viewed 458.4k times · Source

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.

I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a JOIN and therefore is there nothing to worry about?

Answer

Marcelo Cantos picture Marcelo Cantos · Apr 5, 2010

Sub-queries are the logically correct way to solve problems of the form, "Get facts from A, conditional on facts from B". In such instances, it makes more logical sense to stick B in a sub-query than to do a join. It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B.

Practically speaking, however, the answer usually comes down to performance. Some optimisers suck lemons when given a join vs a sub-query, and some suck lemons the other way, and this is optimiser-specific, DBMS-version-specific and query-specific.

Historically, explicit joins usually win, hence the established wisdom that joins are better, but optimisers are getting better all the time, and so I prefer to write queries first in a logically coherent way, and then restructure if performance constraints warrant this.

Related questions

MySQL correlated subquery in JOIN syntax

I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible? The inner query is pivoting rows …

Read More
How do I get a count of associated rows in a left join in MySQL?

I have two tables, a vehicle table with columns: id stock year make model and an images table with columns: id vehicle_id name caption default tinyint(1) I am trying to list the vehicle's information, its default image, and a …

Read More
How to delete from select in MySQL?

This code doesn't work for MySQL 5.0, how to re-write it to make it work DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )) I want to delete columns that dont have unique id. …

Read More