Using LEFT OUTER JOIN With a WHERE Clause

1. Overview

In SQL, joins help retrieve related data from multiple tables. The LEFT OUTER JOIN operation returns all records from the left table, as well as matching records from the right table, inserting NULL values where no match exists. To filter the results further, we can combine this operation with the WHERE clause.

In this tutorial, we’ll explore using the LEFT OUTER JOIN with a WHERE clause in MySQL, PostgreSQL, and SQL Server. We’ll include examples using the Baeldung University schema.

2. Understanding LEFT OUTER JOIN

When using a LEFT OUTER JOIN, the ON clause defines the filtering conditions when joining tables. In particular, these filtering conditions we set in the ON clause influence which records from the right table to include in the join.

Moreover, a LEFT OUTER JOIN requires an ON clause to specify the relationship between tables while joining them. Otherwise, without the ON clause, SQL performs a cross join, where it combines every row from the left table with every row from the right table, which can be inefficient.

Here’s the syntax for using LEFT OUTER JOIN with a WHERE clause:

> SELECT columns
FROM left_table
LEFT OUTER JOIN right_table
ON left_table.common_column = right_table.common_column AND additional_condition;

To demonstrate, we’ll work with two tables, one being the Course table:

> SELECT * FROM Course;
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+-----------+---------------+
| id    | name                                        | textbook                                                               | credits | is_active | department_id |
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+-----------+---------------+
| CE111 | Introduction to Structural Engineering      | Structures : Or Why Things Don't Fall Down by Gordon                   |       7 | Yes       |             4 |
| CE121 | Geotechnical Engineering-I                  | Introduction to Geotechnical Engineering by Das and Sivakugan          |       7 | Yes       |             4 |
...
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        |       7 | Yes       |             1 |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              |       7 | Yes       |             1 |
| CS113 | Introduction to Computer Architecture       | Computer Architecture by Patterson                                     |       7 | Yes       |             1 |
| CS121 | Introduction to Databases                   | Database Systems: The Complete Book                                    |       7 | Yes       |             1 |
...

Above, we display the partial output of courses that belong in the Civil Engineering and Computer Science departments.

The other table we’ll work with is the Department table:

> SELECT * FROM Department;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+
5 rows in set (0.05 sec)

Let’s perform a LEFT OUTER JOIN in which we use Course as the left table and Department as the right table:

> SELECT Course.id, Course.name, Course.textbook, Department.code AS department_code
FROM Course
LEFT OUTER JOIN Department
ON Course.department_id = Department.id AND Department.id < 4;
+-------+---------------------------------------------+------------------------------------------------------------------------+-----------------+
| id    | name                                        | textbook                                                               | department_code |
+-------+---------------------------------------------+------------------------------------------------------------------------+-----------------+
| CE111 | Introduction to Structural Engineering      | Structures : Or Why Things Don't Fall Down by Gordon                   | NULL            |
| CE121 | Geotechnical Engineering-I                  | Introduction to Geotechnical Engineering by Das and Sivakugan          | NULL            |
...
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        | CS              |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              | CS              |
| CS113 | Introduction to Computer Architecture       | Computer Architecture by Patterson                                     | CS              |
| CS121 | Introduction to Databases                   | Database Systems: The Complete Book                                    | CS              |
...

Here’s the breakdown of our command:

• SELECT Clause – selects the id, name, and textbook columns from the Course table and the code column from the Department table, renaming it as department_code in the result set
• FROM Clause – specifies Course as the left table in the query
• LEFT OUTER JOIN Clause – includes all records from the Course table, along with matching records from the Department table
• ON Clause – defines the relationship between Course.department_id and Department.id (Course.department_id = Department.id)
• AND Department.id < 4 – this additional condition affects which records in the Department table are considered for the join (in this case, the records that belong to departments with an id of less than 4)

Now, for a course that belongs to a department with an id of 4 or greater, the department_code field displays NULL.

3. Using WHERE Clause With LEFT OUTER JOIN

We can also apply conditions in the WHERE clause after the join takes place, instead of filtering in the ON clause:

> SELECT columns
FROM left_table
LEFT OUTER JOIN right_table
ON left_table.common_column = right_table.common_column
WHERE condition;

Now, let’s apply this syntax to perform a similar department filtering as the one in section 2, but using the WHERE clause:

> SELECT Course.id, Course.name, Course.textbook, Department.code AS department_code 
FROM Course 
LEFT OUTER JOIN Department ON Course.department_id = Department.id
WHERE Department.id < 4;
+-------+---------------------------------------------+------------------------------------------------------------------------+-----------------+
| id    | name                                        | textbook                                                               | department_code |
+-------+---------------------------------------------+------------------------------------------------------------------------+-----------------+
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        | CS              |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              | CS              |
| CS113 | Introduction to Computer Architecture       | Computer Architecture by Patterson                                     | CS              |
| CS121 | Introduction to Databases                   | Database Systems: The Complete Book                                    | CS              |
...

Initially, the LEFT OUTER JOIN and ON clauses perform the join, including all courses and any matching department records. Thereafter, the WHERE clause filters the final result, removing courses where Department.id >= 4 or is NULL. So, filtering in the WHERE clause eliminates the unmatched rows, unlike filtering in the ON clause, which retains them.

Furthermore, we can modify the WHERE clause to accommodate additional values for department_id in the Course table:

> SELECT Course.id, Course.name, Course.textbook, Department.code AS department_code
FROM Course
LEFT OUTER JOIN Department ON Course.department_id = Department.id
WHERE Department.id < 4 OR Course.department_id IS NULL;

The additional condition Course.department_id IS NULL ensures that courses without a department (where department_id is NULL) are also kept in the final result.

4. Conclusion

In this article, we explored using LEFT OUTER JOIN with a WHERE Clause.

By understanding how filtering conditions influence the query results in LEFT OUTER JOIN, we can properly retrieve data. We demonstrated that filtering in the ON clause determines which rows from the right table are considered during the join, while filtering in the WHERE clause happens afterward, removing unwanted rows from the final result.