UNION, INTERSECT & EXCEPT

Set operations combine the results of two separate SELECT queries as if they were mathematical sets of rows. PostgreSQL supports the three standard set operations: UNION, INTERSECT, and EXCEPT.

Note

Every set operation requires both queries to return the same number of columns, with compatible data types in the same positions. Column names in the final result come from the first query.

UNION and UNION ALL

UNION combines the rows of two queries and removes duplicate rows from the combined result, just like DISTINCT does for a single query.

Every city that has either a customer or a warehouse

SQL

SELECT city FROM customers
UNION
SELECT city FROM warehouses;

UNION ALL keeps every row from both queries, including duplicates.

A combined activity feed of orders and returns, duplicates and all

SQL

SELECT order_id, 'order' AS event_type, order_date AS event_date
FROM orders
UNION ALL
SELECT return_id, 'return' AS event_type, return_date AS event_date
FROM returns;

Tip

Prefer UNION ALL whenever you already know the two queries cannot produce overlapping rows, or you simply don’t care about duplicates. UNION has to sort and de-duplicate the entire combined result, which costs extra work that UNION ALL skips entirely.

INTERSECT

INTERSECT returns only the rows that appear in both result sets.

Customers who have both placed an order and signed up for the newsletter

SQL

SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM newsletter_subscriptions;

EXCEPT

EXCEPT returns the rows from the first query that do not appear in the second query.

Customers who have never placed an order

SQL

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Note

PostgreSQL uses EXCEPT, matching the SQL standard. Oracle uses the non-standard name MINUS for the exact same operation — if you are coming from Oracle, remember to switch to EXCEPT in PostgreSQL.

UNION removes duplicates; UNION ALL keeps everything and is faster
INTERSECT returns rows common to both queries
EXCEPT returns rows in the first query but not the second
All three require the same number of columns with compatible types in both queries