How to get records from one table that does not exist in another?

Problem

You have two tables, tableA and tableB. You want to retrieve all records from tableA that do not have a matching record in tableB based on a specific column.

Input

Let’s start with creating two tables and populating them with data.

tableA – This will be our primary table where we’ll select data from.

tableB – This is the reference table. If an ID from tableA exists in tableB, we don’t want to select it.

tableA

id	name
1	John
2	Jane
3	Doe
4	Smith
5	Eva

tableB

id	description
1	First ID
3	Third ID
5	Fifth ID

Try Hands-On: HERE

Source Tables: Gist

Desired Solution

id	name
2	Jane
4	Smith

Solution 1:

To get the desired records from tableA, you can use a LEFT JOIN or a NOT IN clause. Here’s how you can do it with both methods:

Using LEFT JOIN

    SELECT A.id, A.name
    FROM tableA A
    LEFT JOIN tableB B ON A.id = B.id
    WHERE B.id IS NULL;

Solution 2:

SELECT id, name
FROM tableA
WHERE id NOT IN (SELECT id FROM tableB);

Explanation:

In the LEFT JOIN method, we are joining tableA with tableB based on the id column.

The LEFT JOIN ensures that even if there’s no matching record in tableB, the record from tableA is still returned. We then use the WHERE B.id IS NULL condition to filter out records that do have a match in tableB.

In the NOT IN method, we are simply checking if the id from tableA exists in tableB. If it doesn’t exist, then that record is selected.

Both of these methods will give the desired result, but depending on the data size and indexes, performance can vary, so it’s good to know both approaches.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to access previous row value in SQL?
2. How to exclude specific columns using select except?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?