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.
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.
Try Hands-On: HERE
Source Tables: Gist
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;
SELECT id, name FROM tableA WHERE id NOT IN (SELECT id FROM tableB);
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.