Back to articles list Articles Cookbook
7 minutes read

What Is the Difference Between WHERE and ON in SQL JOINs?

When you join tables in SQL, you may have conditions in an ON clause and in a WHERE clause. Many get confused by the difference between them. In this article, we will discuss this topic by first reminding you the purpose of the ON and WHERE clauses then by demonstrating with examples which types of conditions should be in each of these clauses.

Both the ON clause and the WHERE clause can specify conditions. But are there any differences between them? If so, where should you specify what conditions in your SQL query? Let’s find out together!

ON vs. WHERE Conditions

The purpose of the ON clause is to specify the join conditions, that is, to define how the tables should be joined. Specifically, you define how the records should be matched.

In contrast, the purpose of the WHERE clause is to specify the filtering conditions, that is, to define which rows should be kept in the result set.

Let’s look at an example to understand the difference. We have the following two tables that (1) list the users (the table users) of our rental website and (2) list the houses (the table houses) available for rent.

users
idnameregistration_date
11Jane Stewart2020-11-30
12Mary Cooper2015-06-12
13John Watson2015-01-31
14Christian Wood2018-03-03
15William Grey2021-05-12
16Brandon Evans2018-05-08
17Isabella Gonsalez2020-12-12
18Diana Taylor2020-06-30
19Luke Wilson2019-11-17
20Michael Lee2020-02-15

houses
idaddresscityowner_idbedrooms
101Brook Street, 5Cardiff124
102Richmond Street, 1Cardiff121
103Cromwell Road, 23Liverpool132
104Hastings Road, 109York152
105Bedford Road, 2Bristol161
106Queen Street, 45Bristol163
107Mayfield Road, 34Cardiff123
SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Note that we have conditions in both the ON clause and the WHERE clause:

  • With the ON condition, we specify that the tables be joined by matching the id column in the users table and the owner_id column in the houses
  • With the WHERE condition, we filter the result set by keeping only the users who registered before January 1, 2020.

Thus, we have used the ON and WHERE conditions according to their purpose, resulting in a clear and readable SQL query.

Here is the result set:

idnameregistration_dateaddresscity
12Mary Cooper2015-06-12Brook Street, 5Cardiff
12Mary Cooper2015-06-12Richmond Street, 1Cardiff
13John Watson2015-01-31Cromwell Road, 23Liverpool
16Brandon Evans2018-05-08Bedford Road, 2Bristol
16Brandon Evans2018-05-08Queen Street, 45Bristol
12Mary Cooper2015-06-12Mayfield Road, 34Cardiff

Not sure how the JOIN works in our SQL query? Practice joining tables with this interactive SQL JOINs course.

ON and WHERE Conditions in INNER JOINs

In the above example, we can see how the ON and WHERE conditions are used according to their respective purpose and common practice.

However, it is useful to know that, for (INNER) JOINs, you can specify both the JOIN condition and the filtering condition with an ON clause. For example, we can get the same result as the above with the following SQL query:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

This query is executed in the same way. That said, I do not recommend mixing the join condition and the filtering condition in the same clause. If you compare the two queries, you see the first one is more readable:

  • It’s easier to follow the first query: first, you join the tables by a certain condition, then you filter the result by a different condition.
  • The intent of the entire query is clearer to the outside reader when the conditions are separated by following the rules.

ON and WHERE Conditions in OUTER JOINs

When it comes to OUTER JOINs (i.e., LEFT JOIN, RIGHT JOIN, and FULL JOIN), it is crucial to use the ON and WHERE conditions the way they are intended. Otherwise, you’ll get wrong results. Let’s see with an example.

Again, we want to get the list of users who registered before Jan 1st, 2020, along with their respective houses. This time, however, we want to keep all users, including those that do not have registered houses on our rental website. Thus, we are going to do a LEFT JOIN instead of a JOIN (i.e., an INNER JOIN).

We will see whether there are any differences between specifying the filtering condition in the ON clause and specifying it in the WHERE clause. If we follow the rules and use the conditions as intended, we have the following query:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';
idnameregistration_dateaddresscity
12Mary Cooper2015-06-12Brook Street, 5Cardiff
12Mary Cooper2015-06-12Richmond Street, 1Cardiff
13John Watson2015-01-31Cromwell Road, 23Liverpool
16Brandon Evans2018-05-08Bedford Road, 2Bristol
16Brandon Evans2018-05-08Queen Street, 45Bristol
12Mary Cooper2015-06-12Mayfield Road, 34Cardiff
19Luke Wilson2019-11-17NULLNULL
14Christian Wood2018-03-03NULLNULL

The result looks good. We got all of the users we got in our initial example. In addition, we have two more users who do not have corresponding houses on our website but were included in the result set because of the LEFT JOIN. Note that both registered before January 1, 2020 as specified in our filtering condition.

Do we get the same result if we mix the join condition and the filtering condition in the ON clause? Let’s find out:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';
idnameregistration_dateaddresscity
11Jane Stewart2020-11-30NULLNULL
12Mary Cooper2015-06-12Mayfield Road, 34Cardiff
12Mary Cooper2015-06-12Richmond Street, 1Cardiff
12Mary Cooper2015-06-12Brook Street, 5Cardiff
13John Watson2015-01-31Cromwell Road, 23Liverpool
14Christian Wood2018-03-03NULLNULL
15William Grey2021-05-12NULLNULL
16Brandon Evans2018-05-08Queen Street, 45Bristol
16Brandon Evans2018-05-08Bedford Road, 2Bristol
17Isabella Gonsalez2020-12-12NULLNULL
18Diana Taylor2020-06-30NULLNULL
19Luke Wilson2019-11-17NULLNULL
20Michael Lee2020-02-15NULLNULL

As you can see, the results are different. We have all the users included, even the ones who registered in 2020 or 2021. This is because the LEFT JOIN keeps all the records from the left table even when the ON logic fails. So, in this example, specifying the filtering condition in the ON clause doesn’t work for us. To get the correct result, we need to specify the conditions as intended.

Interestingly, there are situations in which the WHERE condition can “cancel” the intent of an OUTER JOIN. As an example, let’s say we want to list all users with their corresponding houses but only if the houses have 3 or more bedrooms.

Since we want to keep all users, we will use an OUTER JOIN, specifically a LEFT JOIN. Our requirement for the number of bedrooms is clearly a filtering condition. So, we’ll include it in the WHERE clause. Here’s our SQL query with conditions specified as intended:

SELECT u.id, u.name, h.address, h.city, h.bedrooms
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE h.bedrooms > 2;

Doesn’t seem right, does it? The result looks as if we used an INNER JOIN rather than a LEFT JOIN. Users without houses are not included in the resulting table, because they have NULL in the bedrooms column when the tables are joined. Since the NULL values are considered less than 0, the corresponding rows are removed when we apply the filtering condition – the number of bedrooms greater than 2.

There are two possible solutions to this problem:

  • Add another filtering condition to the WHERE clause, like bedrooms is NULL:
    SELECT u.id, u.name, h.address, h.city, h.bedrooms
    FROM users u
    LEFT JOIN houses h
    ON u.id = h.owner_id
    WHERE h.bedrooms > 2 OR h.bedrooms is NULL;
    
  • Moving the filtering condition to the ON clause:
    SELECT u.id, u.name, h.address, h.city, h.bedrooms
    FROM users u
    LEFT JOIN houses h
    ON u.id = h.owner_id AND h.bedrooms > 2;
    

Either of these queries gives us the following result:



idnameaddresscitybedrooms
11Jane StewartNULLNULLNULL
12Mary CooperMayfield Road, 34Cardiff3
12Mary CooperBrook Street, 5Cardiff4
13John WatsonCromwell Road, 23LiverpoolNULL
14Christian WoodNULLNULLNULL
15William GreyNULLNULLNULL
16Brandon EvansQueen Street, 45Bristol3
17Isabella GonsalezNULLNULLNULL
18Diana TaylorNULLNULLNULL
19Luke WilsonNULLNULLNULL
20Michael LeeNULLNULLNULL

Now you know! In OUTER JOINs, it does make a difference how we specify the conditions.

Let’s Practice JOINs in SQL!

SQL JOINs are not too difficult to understand. However, as you could see from the examples in this article, there are nuances that should be considered when joining tables and writing join conditions in SQL.

If you really want to master SQL JOINs, practicing with real-world data sets is a key success factor. I recommend starting with the interactive SQL JOINs course – it includes 93 coding challenges covering the most common types of joins like JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and even self-joins and non-equi joins. After taking this course, you’ll know how to join multiple tables, how to join tables without a common column, and how to correctly filter data with different kinds of JOINs.

For those wanting experience with even more SQL JOIN use cases, I recommend taking the SQL Practice track. It includes five interactive courses with 600+ coding challenges, covering not only the basics of SQL JOINs but also how to filter the result set with a WHERE clause, how to aggregate data with GROUP BY and HAVING, and how to use subqueries including correlated subqueries. You’re going to have lots of fun!

BONUS. Here’re the top 10 SQL JOIN interview questions with answers.