UNION, INTERSECT and EXCEPT

Set semantics

Set operations combine query results with the same number and types of columns. UNION removes duplicates (with a sort or hash cost) while UNION ALL keeps them and is cheaper, so use it when duplicates are impossible or wanted. INTERSECT returns rows in both results and EXCEPT rows in the first but not the second. Set operations compare NULLs as equal, unlike =.

Four set operations, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). UNION deduplicates cities and adds Mumbai; UNION ALL keeps all 8 rows including NULL cities; customer 3 ordered in February but not January; customers 2 and 3 have both paid and refunded or cancelled orders.

SELECT city FROM customers WHERE city IS NOT NULL
UNION SELECT 'Mumbai' ORDER BY 1;

SELECT count(*) AS union_all_rows FROM (
  SELECT city FROM customers UNION ALL SELECT city FROM customers) t;

-- Customers who ordered in February but not in January
SELECT customer_id FROM orders WHERE ordered_on >= '2026-02-01' AND ordered_on < '2026-03-01'
EXCEPT
SELECT customer_id FROM orders WHERE ordered_on < '2026-02-01';

SELECT customer_id FROM orders WHERE status = 'paid'
INTERSECT
SELECT customer_id FROM orders WHERE status IN ('refunded', 'cancelled') ORDER BY 1;

Output:

  city  
--------
 Delhi
 Mumbai
 Pune
(3 rows)

 union_all_rows 
----------------
              8
(1 row)

 customer_id 
-------------
           3
(1 row)

 customer_id 
-------------
           2
           3
(2 rows)

Prefer UNION ALL by default

Only pay for duplicate removal when you actually need it.

Quick check: Which operation returns rows in the first query but not the second?

• EXCEPT
• INTERSECT
• UNION
• UNION ALL