INTERSECT and EXCEPT in PostgreSQL

Introduction

Use INTERSECT and EXCEPT to find common or different data between two lists or tables easily.

You want to find items that appear in both of two lists, like customers who bought both product A and product B.

You want to find items in one list but not in another, like employees who worked last year but not this year.

You want to compare two sets of data to see what is shared or what is missing.

You want to clean up duplicates by finding common entries between two sources.

You want to check differences between two reports or tables quickly.

Syntax

PostgreSQL

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;

SELECT column_list FROM table1
EXCEPT
SELECT column_list FROM table2;

Both queries must select the same number of columns with compatible data types.

INTERSECT returns rows common to both queries; EXCEPT returns rows in the first query but not in the second.

Examples

Finds employees who worked in both 2023 and 2024.

PostgreSQL

SELECT name FROM employees_2023
INTERSECT
SELECT name FROM employees_2024;

Finds products sold in January but not in February.

PostgreSQL

SELECT product_id FROM sales_january
EXCEPT
SELECT product_id FROM sales_february;

Finds cities where customers live in both US and Canada.

PostgreSQL

SELECT city FROM customers_us
INTERSECT
SELECT city FROM customers_canada;

Sample Program

This example creates two fruit lists and shows how to find common fruits and fruits only in the first list.

PostgreSQL

CREATE TABLE fruits1 (name TEXT);
CREATE TABLE fruits2 (name TEXT);

INSERT INTO fruits1 VALUES ('apple'), ('banana'), ('cherry');
INSERT INTO fruits2 VALUES ('banana'), ('cherry'), ('date');

-- Find fruits in both lists
SELECT name FROM fruits1
INTERSECT
SELECT name FROM fruits2;

-- Find fruits in fruits1 but not in fruits2
SELECT name FROM fruits1
EXCEPT
SELECT name FROM fruits2;

Important Notes

INTERSECT and EXCEPT automatically remove duplicates in the results.

Order of rows in the result is not guaranteed unless you add ORDER BY.

Use UNION if you want all rows from both queries without filtering.

Summary

INTERSECT finds common rows between two queries.

EXCEPT finds rows in the first query that are not in the second.

Both require the same number and type of columns selected.