PostgreSQL - EXCEPT Operator

PostgreSQL - The EXCEPT Operator

The EXCEPT operator in PostgreSQL is used to compare two SELECT statements. It returns all unique rows from the first SELECT statement that are not present in the result set of the second SELECT statement. In set theory terms, it performs a set difference.

Like UNION, the columns in both SELECT statements must have the same number and compatible data types.

Syntax

SELECT column1, column2, ...
FROM table1

EXCEPT

SELECT column1, column2, ...
FROM table2;

Practical Example: Finding Unhired Candidates

A common use case is to find records in one set that don’t exist in another. Let’s say we have a list of all job candidates and another list of candidates who have been hired. We want to find the candidates who are still in the application pool.

Step 1: Create and populate the tables.

CREATE TABLE all_candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL
);

CREATE TABLE hired_candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL
);

-- Populate all candidates
INSERT INTO all_candidates (full_name) VALUES
    ('Alice Johnson'),
    ('Bob Williams'),
    ('Charlie Brown'),
    ('Diana Prince');

-- Populate hired candidates
INSERT INTO hired_candidates (full_name) VALUES
    ('Bob Williams'),
    ('Diana Prince');

Step 2: Use the EXCEPT operator to find the candidates who were not hired.

SELECT full_name
FROM all_candidates

EXCEPT

SELECT full_name
FROM hired_candidates;

The query returns the names from all_candidates that do not appear in hired_candidates:

full_name
Alice Johnson
Charlie Brown

EXCEPT vs. Other Methods

You can achieve a similar result using other techniques, like a LEFT JOIN or NOT IN. Understanding the alternatives helps you choose the best tool for the job.

Using LEFT JOIN

A LEFT JOIN combined with a WHERE ... IS NULL check is a very common pattern:

SELECT ac.full_name
FROM all_candidates AS ac
LEFT JOIN hired_candidates AS hc ON ac.full_name = hc.full_name
WHERE hc.full_name IS NULL;

This query joins the two tables and filters for rows where there was no match in the hired_candidates table, producing the same result. LEFT JOIN can be more flexible if you need to access columns from both tables in your SELECT list or WHERE clause.

Using NOT EXISTS

NOT EXISTS is often the most performant option, especially on large datasets, as it can stop searching as soon as it finds a match.

SELECT ac.full_name
FROM all_candidates AS ac
WHERE NOT EXISTS (
    SELECT 1
    FROM hired_candidates AS hc
    WHERE hc.full_name = ac.full_name
);

When to Use EXCEPT

• Readability: For a simple set difference, EXCEPT is often the most readable and expressive option. Its intent is immediately clear.
• Uniqueness: EXCEPT automatically removes duplicate rows from its final output, just like UNION.
• Simplicity: When you only need columns from the first table and the matching logic is a simple row-for-row comparison, EXCEPT is elegant and concise.