How to Perform an Anti-Join in PostgreSQL

Often in PostgreSQL you may want to perform an anti-join, which returns all rows from one table that do not have a match in another table.

You can use the following basic syntax to do so:

SELECT store1.product, store1.sales
   FROM store1
   WHERE NOT EXISTS
   (
      SELECT 1 FROM store2 WHERE store1.product = store2.product
   );

This particular example will return all rows from the store1 table that do not have a matching value in the product column of the store2 table.

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

Example: How to Perform an Anti-Join in PostgreSQL

Suppose that we create the following table named store1 that contains information about products sold at one retail store:

-- create table named 'store1'
CREATE TABLE store1 (
  product TEXT,
  sales INTEGER
);

-- add rows to the table 
INSERT INTO store1 VALUES ('A', 22);
INSERT INTO store1 VALUES ('B', 16);
INSERT INTO store1 VALUES ('C', 40);
INSERT INTO store1 VALUES ('D', 31);
INSERT INTO store1 VALUES ('E', 12);

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

 product | sales 
---------+-------
 A       |    22
 B       |    16
 C       |    40
 D       |    31
 E       |    12

Then suppose we create another table named store2 that contains information about products sold at another retail store:

-- create table named 'store2'
CREATE TABLE store2 (
  product TEXT,
  sales INTEGER
);

-- add rows to the table 
INSERT INTO store2 VALUES ('A', 22);
INSERT INTO store2 VALUES ('B', 16);
INSERT INTO store2 VALUES ('C', 40);
INSERT INTO store2 VALUES ('F', 31);
INSERT INTO store2 VALUES ('G', 12);

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

 product | sales 
---------+-------
 A       |    22
 B       |    16
 C       |    23
 F       |    31
 G       |    12

We can use the following syntax to perform an anti-join to only return the rows from the store1 table that do not have matching values in the product column in the store2 table:

SELECT store1.product, store1.sales
   FROM store1
   WHERE NOT EXISTS
   (
      SELECT 1 FROM store2 WHERE store1.product = store2.product
   );

 product | sales 
---------+-------
 D       |    31
 E       |    12

Notice that the resulting table only contains rows from the store1 table that do not have matching values in the product column in the store2 table.

You can use similar syntax to perform an anti-join using your own tables in PostgreSQL.

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