Set Operations

Set operations combine the results of two or more SELECT statements into a single result set.

Rules

• All queries must return the same number of columns.
• Corresponding columns must have compatible types.
• Column names come from the first query.
• ORDER BY and LIMIT can be applied to the final combined result by placing them after the last query.

UNION

Combines results from two queries and removes duplicate rows.

SELECT name, email FROM customers
UNION
SELECT name, email FROM prospects;

Example

-- Table A: (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
-- Table B: (2, 'Bob'), (4, 'Dave')

SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;

Result:

UNION ALL

Combines results from two queries and keeps all rows, including duplicates. Faster than UNION because no deduplication is needed.

SELECT id, name FROM table_a
UNION ALL
SELECT id, name FROM table_b;

Result:

-- Combine multiple data sources
SELECT * FROM read_csv('jan_sales.csv')
UNION ALL
SELECT * FROM read_csv('feb_sales.csv')
UNION ALL
SELECT * FROM read_csv('mar_sales.csv');

INTERSECT

Returns only rows that appear in both result sets.

SELECT id, name FROM table_a
INTERSECT
SELECT id, name FROM table_b;

Result:

-- Find customers who are also prospects
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;

EXCEPT

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

SELECT id, name FROM table_a
EXCEPT
SELECT id, name FROM table_b;

Result:

-- Customers who have never placed an order
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders;

Ordering and Limiting Combined Results

-- ORDER BY and LIMIT apply to the final combined result
SELECT name, 'customer' AS source FROM customers
UNION ALL
SELECT name, 'prospect' AS source FROM prospects
ORDER BY name ASC
LIMIT 20;

Chaining Set Operations

-- Combine three queries
SELECT name FROM engineers
UNION
SELECT name FROM designers
UNION
SELECT name FROM managers
ORDER BY name;

Comparison Summary