PostgreSQL: Select Rows That Do Not Exist in Another Table

Often in PostgreSQL you may want to select all rows from one table that do not exist in another table.

You can use the following basic syntax to do so:

SELECT a1.id, a1.team, a1.points
FROM athletes1 a1
LEFT JOIN athletes2 a2 USING (id)
WHERE a2.id IS NULL;

This particular example will select all rows from the table named athletes1 where the value in the id column of the table does not exist in the id column of the table named athletes2.

Here is how this syntax works:

• First, we perform a LEFT JOIN on the two tables, which ensures that all rows from the athletes1 table are returned.
• Then, we use a WHERE statement to only return the rows where the value from the id column of the athletes2 table is NULL, i.e. the id value only exists in the athletes1 table.

The following example shows how to use this syntax in practice.

Example: How to Select Rows Not in Another Table in PostgreSQL

Suppose that we create the following table named athletes1 that contains information about various basketball players:

-- create table named 'athletes1'
CREATE TABLE athletes1 (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes1 VALUES (0001, 'Mavs', 15);
INSERT INTO athletes1 VALUES (0002, 'Hawks', 12);
INSERT INTO athletes1 VALUES (0003, 'Rockets', 23);
INSERT INTO athletes1 VALUES (0004, 'Spurs', 29);
INSERT INTO athletes1 VALUES (0005, 'Kings', 35);

-- view all rows in table
SELECT * FROM athletes1;

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     15
  2 | Hawks   |     12
  3 | Rockets |     23
  4 | Spurs   |     29
  5 | Kings   |     35

Then suppose we create another table named athletes2 that also contains information about various basketball players:

-- create table named 'athletes2'
CREATE TABLE athletes2 (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes2 VALUES (0001, 'Mavs', 15);
INSERT INTO athletes2 VALUES (0002, 'Hawks', 12);
INSERT INTO athletes2 VALUES (0006, 'Lakers', 34);
INSERT INTO athletes2 VALUES (0007, 'Celtics', 18);
INSERT INTO athletes2 VALUES (0008, 'Nuggets', 10);

-- view all rows in table
SELECT * FROM athletes2;

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     15
  2 | Hawks   |     12
  6 | Lakers  |     34
  7 | Celtics |     18
  8 | Nuggets |     10

We can use the following syntax to only select the rows from the athletes1 table where the value in the id column does not exist in the athletes2 table:

SELECT a1.id, a1.team, a1.points
FROM athletes1 a1
LEFT JOIN athletes2 a2 USING (id)
WHERE a2.id IS NULL;

 id |  team   | points 
----+---------+--------
  3 | Rockets |     23
  4 | Spurs   |     29
  5 | Kings   |     35

Notice that the resulting table only contains the rows from the athletes1 table where the value in the id column of the table does not also exist in the athletes2 table.

Note: Simply change id to a different column name to instead perform a join using a different column.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Select Range of Rows from Table
PostgreSQL: How to Calculate Difference Between Rows