Set Operations

Combine result sets from multiple queries with UNION, INTERSECT, and EXCEPT.

UNION ALL

SELECT id, val FROM s1
UNION ALL
SELECT id, val FROM s2;
-- Keeps all rows including duplicates: 6 rows

UNION

SELECT id, val FROM s1
UNION
SELECT id, val FROM s2;
-- Removes duplicates: 4 rows (1,a), (2,b), (3,c), (4,d)

INTERSECT

SELECT id, val FROM s1
INTERSECT
SELECT id, val FROM s2;
-- Only matching rows: (2,b), (3,c)

EXCEPT

SELECT id, val FROM s1
EXCEPT
SELECT id, val FROM s2;
-- In s1 but not s2: (1,a)

SELECT id, val FROM s2
EXCEPT
SELECT id, val FROM s1;
-- In s2 but not s1: (4,d)

With ORDER BY and LIMIT

SELECT id, val FROM s1
UNION ALL
SELECT id, val FROM s2
ORDER BY id
LIMIT 3;
-- (1,a), (2,b), (2,b)

Chained Set Operations

SELECT id, val FROM s1
UNION ALL SELECT id, val FROM s2
UNION ALL SELECT id, val FROM s3;
-- All rows from all three tables