Difference Between WHERE Clause and ON Clause in JOIN

1. Introduction

In an SQL database, we use tables to store data. By using the JOIN statement, we can combine data from two tables into a single result set. In addition, WHERE and ON clauses are often utilized to specify the conditions for joining the tables. Although these two clauses might return the same results in some cases, they serve different purposes in the JOIN operation.

In this tutorial, we’ll introduce the JOIN operation with some example queries. Furthermore, we’ll explain the key differences between WHERE and ON clauses in various types of JOIN statements.

2. JOIN Statement

When designing a database, related data is often stored in multiple tables to reduce redundancy and interconnectivity. Consequently, joining two tables can provide meaningful insights from the entities across these tables. A JOIN statement specifies how tables should be combined in a query.

There are five types of joins in SQL:

• INNER JOIN
• LEFT JOIN (LEFT OUTER JOIN)
• RIGHT JOIN (RIGHT OUTER JOIN)
• FULL OUTER JOIN
• CROSS JOIN

Let’s see a figure that illustrates different kinds of JOINs with tables A and B:

As shown in the figure, the ON and WHERE clauses are crucial when performing joins in SQL.

3. ON Clause and WHERE Clause

Both ON and WHERE clauses can be used to specify the condition for combining two tables.

Let’s use the two tables, Course and Department, from the Baeldung University schema to illustrate the usage of ON and WHERE clauses. The table Course contains records for each course, including the course id, name, textbook, credits, department_id, and similar columns. On the other hand, the Department table stores the information for each department, such as the department id, name, and code.

The Course table has a column named department_id, which links to the id column in the Department table. We can join these two tables using either ON or WHERE clauses, resulting in the department name and code matching the course information based on the department id:

SELECT Course.id, Course.name, Course.credits, Course.is_active, Course.department_id, Department.name, Department.code
FROM Course
JOIN Department
ON Course.department_id = Department.id;

This query returns 81 rows:

+-------+---------------------------------------------+---------+-----------+---------------+--------------------------------+------+
| id    | name                                        | credits | is_active | department_id | name                           | code |
+-------+---------------------------------------------+---------+-----------+---------------+--------------------------------+------+
| CS111 | Introduction to Operating Systems           |       7 | Yes       |             1 | Computer Science               | CS   |
| CS112 | Introduction to Real Time Operating Systems |       7 | Yes       |             1 | Computer Science               | CS   |
| CS113 | Introduction to Computer Architecture       |       7 | Yes       |             1 | Computer Science               | CS   |
| CS121 | Introduction to Databases                   |       7 | Yes       |             1 | Computer Science               | CS   |
| CS122 | Relational Databases                        |       7 | No        |             1 | Computer Science               | CS   |
| CS123 | Introduction to Structured Query Language   |       7 | Yes       |             1 | Computer Science               | CS   |
...
| MA411 | Linear Algebra-III                          |       5 | Yes       |             5 | Mathematics                    | MA   |
| MA421 | Discrete Maths-III                          |       5 | Yes       |             5 | Mathematics                    | MA   |
| MA431 | Advanced Calculus                           |       5 | Yes       |             5 | Mathematics                    | MA   |
| MA441 | Advanced Optimizaton                        |       5 | No        |             5 | Mathematics                    | MA   |
+-------+---------------------------------------------+---------+-----------+---------------+--------------------------------+------+
81 rows in set (0.00 sec)

We get the same result set using the WHERE clause:

SELECT Course.id, Course.name, Course.credits, Course.is_active, Course.department_id, Department.name, Department.code
FROM Course
JOIN Department
WHERE Course.department_id = Department.id;

In this example, the JOIN statement performs INNER JOIN between the two tables. Although both queries return the same result, they serve different purposes.

3.1. ON Clause

We leverage the ON clause in the JOIN statement to specify the conditions for joining the tables.

In the example query using the ON clause, it specifies that the tables are joined when the department_id from the Course table matches the id from the Department table. Only the rows satisfying this condition are combined and included in the final result set.

3.2. WHERE Clause

Unlike the ON clause, we use the WHERE clause to filter records after the JOIN has been performed. When we use the WHERE clause instead of the ON clause to JOIN the tables, it functions as an implicit JOIN condition for an INNER JOIN.

The WHERE clause can introduce extra conditions to filter the result set, enabling us to exclude rows that don’t meet specific criteria:

SELECT Course.id, Course.name, Course.credits, Course.is_active, Course.department_id, Department.name, Department.code 
FROM Course
JOIN Department
ON Course.department_id = Department.id
WHERE Department.name = 'Computer Science';

This query first joins the two tables using ON and then filters the courses by department name, resulting in 31 rows.

4. Key Differences Between ON and WHERE

Even though the ON and WHERE clauses can select and combine the records from two tables meeting certain criteria. They have different purposes and orders of evaluation.

4.1. Purpose

The ON clause defines the condition between joined tables.

On the other hand, the WHERE clause focuses on filtering results.

4.2. Order of Evaluation

The ON clause is evaluated during the JOIN process.

However, the WHERE clause is evaluated based on the results after the JOIN process.

4.3. OUTER JOIN

The results we get from using either the ON clause or the WHERE clause can be the same when using INNER JOIN. However, with an OUTER JOIN, different clauses result in distinct outputs.

When using an INNER JOIN, both ON and WHERE clauses require Course.deparment_id = Deparment.id. Hence, the JOIN statement filters any rows where the column used for joining has NULL values, regardless of the clause used.

In contrast, when we use an OUTER JOIN (for example, LEFT JOIN), the queries return all the rows from the left table, regardless of NULL.

For example, let’s see a query that returns all the rows from the Course table and for the rows that don’t have a match in the Department table, the column Department.name, it returns NULL:

SELECT Course.name, Department.name
FROM Course
LEFT JOIN Department
ON Course.department_id = Department.id;

Nevertheless, if we use the WHERE clause instead, the query filters out the records in the Course table that don’t match those in the Department table.

5. Conclusion

In this article, we discussed the usage of ON and WHERE clauses in JOIN statements.

Although the distinction between the ON clause and the WHERE clause is subtle in INNER JOIN, the purpose and order of evaluation still differ between clauses. However, when applying OUTER JOIN, using different clauses can affect the output of the JOIN queries.