When to use join and when to use subselects

2007-09-16

Many people ask this question. The answer is very simple, it depends on the context.

First, let's explain how joins work.

When you join two tables, one temporary table is generated containing columns from both tables. So, you get one big table that has all information you will need from your query.

How do subselects work ?

Subselects work by executing the nested SQL query and returning the needed row(s). So, there is an overhead, for each row from your primary query. Then, an additional sql statement is executed. But, don't forget query cache. If your sql returns the same data each time it is executed, then the overhead is minimum because the data is cached like in this case :

SELECT * FROM articles WHERE author = (SELECT author_id FROM authors WHERE author_name = 'codeassembly');

But, if your subquery returns different data each time it is executed, then the data is not cached and your primary sql will run slow.Here is an example:

SELECT *,(SELECT author_name FROM authors WHERE author_name = articles.author)) as author FROM articles WHERE author;

The second sql can be faster if rewritten like this (using join):

SELECT * FROM articles,authors WHERE authors.author_id = articles.author;

I think you got the idea. If you have a simple sql, that returns the same results every time or if you have a join that returns only one row, then it's faster to use subselects. But, if you have many rows and your subquery does not return the same data for each row, then you must use join.