SQL UNION: Combine Results From Multiple Queries

A JOIN puts columns from two tables side by side. UNION does something different: it stacks the rows of two queries on top of each other into one result, and removes duplicates.

The example tables#

employees: name, city

customers: name, city

Syntax#

SELECT columns FROM table_a
UNION
SELECT columns FROM table_b;

Example: every city we operate in#

SELECT city FROM employees
UNION
SELECT city FROM customers
ORDER BY city;

city
---------
Bengaluru
Chennai
Delhi
Mumbai
Pune

Employees live in 4 cities and customers in 4 cities, but the combined list has 5, because UNION removed the cities that appear in both (and the repeated Mumbai).

The rules#

1. Each SELECT must return the same number of columns.
2. Columns are matched by position, not by name, and must have compatible types.
3. The column names in the result come from the first query.
4. ORDER BY goes once, at the very end, and sorts the combined result.

Labelling where each row came from#

Add a constant column to each part:

SELECT name, 'Employee' AS type FROM employees WHERE city = 'Mumbai'
UNION
SELECT name, 'Customer'         FROM customers WHERE city = 'Mumbai'
ORDER BY type, name;

name | type
-----+---------
Asha | Customer
Asha | Employee
John | Employee

Asha appears twice because the two rows are different (Customer vs Employee). UNION only removes rows that are identical in every column.

UNION vs UNION ALL#

UNION removes duplicates; UNION ALL keeps them. Removing duplicates means the database must sort or hash the combined result, so UNION ALL is faster. Use UNION only when you actually need duplicates removed.

UNION vs JOIN#

INTERSECT and EXCEPT#

Two related operators compare result sets:

-- Names that are both an employee and a customer
SELECT name FROM employees
INTERSECT
SELECT name FROM customers
ORDER BY name;

name
-----
Asha
John
Meera
Ravi

-- Employee names that are not customer names
SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

name
-----
Priya

INTERSECT and EXCEPT work in PostgreSQL, SQL Server, SQLite, and MySQL 8.0.31+. Oracle calls EXCEPT MINUS.

Common mistakes#

Practice#

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

name
-----
Asha
John
Meera
Priya
Ravi

Key takeaway#