Back to articles list July 22, 2021 - 7 minutes read What Is the Difference Between WHERE and ON in SQL JOINs? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql sql joins WHERE 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. Tags: sql learn sql sql joins WHERE You may also like SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more The Top 10 SQL JOIN Interview Questions and How to Answer Them Do you have an SQL interview coming up? This article will help you prepare. Learn how to answer the most common SQL JOIN interview questions. Read more Top 10 SQL JOIN Articles for Beginners Are you a SQL JOIN beginner? Want to read up on the subject? Here’s our list of ten pieces that will give you a sound basis for confidence. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.