SQL UNION and set operations

Combine the results of several queries with UNION and related set operations.

Set operations combine result sets from multiple SELECT statements.

UNION

Combines results and removes duplicates:

-- Combine customers and suppliers into one list
SELECT name, email, 'Customer' AS type FROM customers
UNION
SELECT name, email, 'Supplier' AS type FROM suppliers;

-- Note: Columns must match in number and type

UNION ALL

Combines results and keeps all duplicates (faster):

-- All transactions (even duplicates)
SELECT amount, 'Credit' AS type FROM credits
UNION ALL
SELECT amount, 'Debit' AS type FROM debits;

INTERSECT

Returns only rows that appear in both queries:

-- Customers who are also suppliers
SELECT name FROM customers
INTERSECT
SELECT name FROM suppliers;

EXCEPT (MINUS)

Returns rows from first query that aren't in second:

-- Customers who are NOT suppliers
SELECT name FROM customers
EXCEPT
SELECT name FROM suppliers;

-- Note: Oracle uses MINUS instead of EXCEPT

Rules

- All queries must have the same number of columns
- Column data types must be compatible
- Column names come from the first query
- ORDER BY goes at the very end

Try It Yourself

CREATE TABLE customers (name TEXT);
CREATE TABLE suppliers (name TEXT);
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
INSERT INTO suppliers (name) VALUES ('Bob'), ('Dana');

SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;

name
-----
Alice
Bob
Dana

Change UNION to UNION ALL and Bob appears twice. INTERSECT returns only Bob, and EXCEPT returns only Alice, because the first query minus the second leaves Alice. SQL Server and PostgreSQL support all four operators. MySQL added INTERSECT and EXCEPT in version 8.0.31.