SQL Tutorial · Joins and Subqueries · Lesson 23 of 36

SQL UNION

Stack the results of two queries with UNION and UNION ALL.

UNION

UNION combines the rows of two queries with the same number of columns and removes duplicates. UNION ALL keeps duplicates — and is faster, so use it when duplicates are impossible or wanted:

Example 1Try it yourself

SELECT city FROM customers WHERE country = 'USA'
UNION
SELECT 'Head office: ' || 'London'
ORDER BY 1;
SELECT 'customer' AS kind, name FROM customers WHERE name LIKE 'A%'
UNION ALL
SELECT 'employee', name FROM employees WHERE name LIKE 'D%';

Output

city
NULL
Boston
Hampton
Head office: London
New York
Portland

kind | name
customer | Ada Lovelace
customer | Alan Turing
employee | Diego Silva

INTERSECT and EXCEPT

INTERSECT keeps rows present in both results; EXCEPT (MINUS in Oracle) keeps rows from the first that aren't in the second:

Example 2Try it yourself

-- Customers who ordered in January AND in May
SELECT customer_id FROM orders WHERE order_date LIKE '2026-01%'
INTERSECT
SELECT customer_id FROM orders WHERE order_date LIKE '2026-05%';
-- Customers who ordered in Q1 but never later
SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01';

Output

customer_id
2

customer_id
3
6

Test yourself

Which operator combines results and keeps duplicates?

UNION
UNION ALL
JOIN
INTERSECT

Show answer

Answer: UNION ALL