SQL Common Errors: confusing WHERE and ON

You probably know the ON clause of SQL JOINs. It is a condition that determines which rows from a table are related to a row from another table.

Conceptually, this is extremely different from the WHERE clause, that determines which row combinations will be returned to the client. However, sometimes a condition can be moved from the WHERE clause to the ON clause, because it will still prevent rows from being excluded. But please keep on reading, because the TL;DR of this post is: don’t do that.

Actually, why would one move conditions from WHERE to ON? Because many developers think that this is an optimization. They believe that the ON clause is executed before the WHERE clause, so moving the conditions will cause them to be evaluated at an earlier stage, avoiding some useless work.

Now, one may actually show some examples where moving a condition actually speeds up a query. This can happen in some uncommon cases, and the root cause is that the optimizer is not able to find the optimal execution plan. Don’t focus on the ON clause. Just focus on building proper indexes or finding out why the optimizer is not using them. But this is a wider topic that will not be covered here.

ON means not WHERE

Let me show you an example. I used MariaDB here, but I expect the same results with all DBMSs I know.

SELECT a.full_name, b.title
    FROM author a
    LEFT JOIN book b
        ON a.id = b.author_id
    WHERE b.title LIKE 'the%'
;
+------------+-----------------------------+
| full_name  | title                       |
+------------+-----------------------------+
| H.G. Wells | The invisible man           |
| H.G. Wells | The Island of Doctor Moreau |
+------------+-----------------------------+
2 rows in set (0.000 sec)

We get a list of the books whose title starts with “the” (case-insensitive), and their authors. As you can see, the ON clause is used to join the rows correctly, and the WHERE clause determines which rows are to be filtered in.

In this database, I inserted another author: Edgar Allan Poe. But he doesn’t appear, because none of the books I inserted starts with “The”.

But what if I move the WHERE condition to ON?

SELECT a.full_name, b.title
    FROM author a
    LEFT JOIN book b
        ON a.id = b.author_id
        AND b.title LIKE 'The%'
;
+-----------------+-----------------------------+
| full_name       | title                       |
+-----------------+-----------------------------+
| H.G. Wells      | The invisible man           |
| H.G. Wells      | The Island of Doctor Moreau |
| Edgar Allan Poe | NULL                        |
+-----------------+-----------------------------+
3 rows in set (0.000 sec)

Edgar Allan Poe appears! Why? SQL is supid!? MariaDB is buggy?! These are typical reactions, but… no, the query is wrong.

This is a LEFT JOIN. Rows from the left tables are returned even if they never match the ON clause.

With an INNER JOIN, actually you will not notice any difference. But what if you are looking for books who title starts with “the” OR whose author name starts with a letter < "M"? Try it yourself, as an exercise. You will see that you cannot always fix the problem by using an INNER JOIN: you need to use ON properly : -)

Federico

Advertisements

3 thoughts on “SQL Common Errors: confusing WHERE and ON

  1. It is not always wrong: I sometimes use the ON clause to limit the set of records from books that I want to consider for the LEFT JOIN

    • In that case it is correct, but it is not an optimization: it is the query you need. My point is about using ON as a performance optimization. Many developers think that it is faster (it isn’t, usually) and they don’t understand that it does something different.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s