Last Updated: February 25, 2016
· hannesg

Consider subqueries instead of joins

Doing inner joins is a pretty common technique in many databases. They are used for many different things like fetching rows from multiple tables at once as well as filtering and sorting by values from multiple tables. However, this versatility has its downsides. Take for example the following query:

     WHERE title LIKE "%foo%";

For some reasons the query must be extended to also filter by author:

    INNER JOIN Author ON Book.author_id =
    WHERE Book.title LIKE "%foo%"
    AND = "Bar";

Notice something? Just for adding ONE additional condition, the query had to be changed in THREE different places ( adding the join, adding the condition, qualifing all columns ). Furthermore, you have added a lot of clutter to the namespace. Just imagine the query getting longer. Will it be obvious that this join was added to filter by name? I guess not!
A more elegant way to write this is using subqueries:

    WHERE title LIKE "%foo%"
    AND author_id IN
        ( SELECT id FROM Author WHERE name = "Bar" );

BÄM! To add ONE condition, the query was changed in ONE place. This is much cleaner, a bit shorter and does not add junk to the namespace. Even if the query becomes bigger the subquery stays inside the WHERE clause.

One reason against subqueries was always that they don't have the same performance as joins. This is not true anymore on modern database systems. Take MySQL 5 and Postgres 8. Both systems recognize subqueries that are independent of the outer query and handle them exactly like inner joins. So the performance is just the same!

1 Response
Add your response

I like that one, especially the downgrading of the performance issue makes this tip interesting.

over 1 year ago ·