Selecting All Records From One Table That Don’t Exist in Another Table in SQL

1. Overview

In SQL, selecting rows from one table that don’t exist in another table is crucial. This identification of data among tables is beneficial for data analysis and manipulation tasks. Furthermore, it helps to compare data from multiple tables.

In this tutorial, we’ll look at different ways to perform such operations and their various syntaxes.

2. Using LEFT JOIN With IS NULL

Before we move forward, let’s check the records in the table. As an example, we’ll use the Baeldung University schema.

Let’s fetch the required data from the Course table:

SELECT c.*
FROM Course c
LEFT JOIN Registration r ON c.id = r.course_id
WHERE r.course_id IS NULL;
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| id    | name                                | textbook                            | credits | is_active | department_id |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| CS213 | Computer Architecture: Intermediate | Computer Architecture by Patterson  |       7 | Yes       |             1 |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+

In the query above, we used the LEFT JOIN and it includes all the records from the course and matches records from the registration table. Furthermore, in this query where r.course_id IS NULL filters out the courses that have registrations, leaving only those without registrations.

LEFT JOIN is a cartesian join that matches each row of one table with every row of another table, so it can be resource-intensive. However, it is useful for small datasets.

3. Using NOT EXISTS

The EXISTS operator is used to check the existence of any record in a sub-query. The EXISTS operator returns true if at least one record satisfies that particular condition of the query:

SELECT c.* 
FROM Course c 
WHERE NOT EXISTS (
    SELECT 1 
    FROM Registration r 
    WHERE c.id = r.course_id
);
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| id    | name                                | textbook                            | credits | is_active | department_id |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| CS213 | Computer Architecture: Intermediate | Computer Architecture by Patterson  |       7 | Yes       |             1 |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+

The above sub-query checks if there is any matching record present in the registration table. In addition, NOT EXISTS ensures that only courses without matching registrations are selected.

In large datasets, NOT EXISTS can be more efficient than LEFT JOIN because it short-circuits as soon as it finds a match. Mainly, it is preferred when a subquery returns a small number of results.

4. Using NOT IN

The NOT IN operator in SQL is used to fetch all the records that aren’t present based on a particular field. We can use this operator to select records from one table that aren’t present in another table:

SELECT *
FROM Course c
WHERE c.id NOT IN (
    SELECT r.course_id 
    FROM Registration r
);
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| id    | name                                | textbook                            | credits | is_active | department_id |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| CS213 | Computer Architecture: Intermediate | Computer Architecture by Patterson  |       7 | Yes       |             1 |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+

The sub-query inside the query first fetches all the course IDs from the registration, and then using the NOT IN operator filters out all the course IDs in the list returned by the sub-query.

When evaluating large datasets, the NOT IN operator could potentially cause performance issues, as it does not short-circuit and evaluates the entire subquery.

5. Using EXCEPT Operator in SQL Server

EXCEPT is a special operator in SQL Server that returns all the records from the first select query that are not present in the other query:

SELECT *
FROM Course c
EXCEPT
SELECT c.id, c.name, c.textbook, c.credits, c.is_active, c.department_id
FROM Course c
JOIN Registration r ON c.id = r.course_id;
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| id    | name                                | textbook                            | credits | is_active | department_id |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+
| CS213 | Computer Architecture: Intermediate | Computer Architecture by Patterson  |       7 | Yes       |             1 |
+-------+-------------------------------------+-------------------------------------+---------+-----------+---------------+

The first select query fetches all the records from the Course table. Furthermore, the second query fetches all the courses in the Registration table. The EXCEPT operator simply returns all the courses present in the first table and not in the second.

6. Conclusion

In this article, we explored various solutions to fetch all the records from one table that aren’t present in another table. We looked at different operators to fetch different results. Each method has its advantages and disadvantages as per performance metrics. Understanding every method helps us write an efficient query for a particular use case.