cmm32a
Last Updated: February 25, 2016
·
4.525K
· hannesg
546cb399441344941ff1052fae9563b4

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:

SELECT * FROM Book
     WHERE title LIKE "%foo%";

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

SELECT Book.* FROM Book
    INNER JOIN Author ON Book.author_id = Author.id
    WHERE Book.title LIKE "%foo%"
    AND Author.name = "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:

SELECT * FROM Book
    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!

Say Thanks
Respond

1 Response
Add your response

1655
6b4b7f11163b1e87571995ce8c3f0bce

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

over 1 year ago ·