SQL UNION and UNION ALL: Syntax, Differences & Examples

The Playground database stores customers in users and staff in employees. To list every city where the company has either a customer or an employee, run one query per table and put UNION between them:

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

▶ Run this query in the SQL Playground

The first query returns one city per customer and the second one city per employee. UNION stacks the two lists and then removes duplicates, so each city appears once: 20 rows in the result.

Use UNION when the rows you want live in more than one table (or more than one query) but have the same shape. A JOIN is the tool when you want to put related columns side by side instead.

SELECT column_1, column_2 FROM table_a
UNION [ALL]
SELECT column_1, column_2 FROM table_b
ORDER BY column_1;

Four rules apply in every database:

UNION ALL skips the duplicate check and returns every row from every query. Count the rows each version produces for the same two queries:

SELECT 'UNION ALL' AS operator, COUNT(*) AS rows_returned
FROM (SELECT city FROM users UNION ALL SELECT city FROM employees) AS t
UNION ALL
SELECT 'UNION', COUNT(*)
FROM (SELECT city FROM users UNION SELECT city FROM employees) AS t;

▶ Run this query in the SQL Playground

UNION ALL returns 270 rows, one for every customer and employee. UNION returns 20, because it keeps only one copy of each city.

Once rows are stacked you can no longer tell which table they came from. Add a constant column to each query to keep track:

SELECT first_name, last_name, 'customer' AS source
FROM users
WHERE city = 'Austin'
UNION ALL
SELECT first_name, last_name, 'employee'
FROM employees
WHERE city = 'Austin'
ORDER BY source, last_name, first_name;

▶ Run this query in the SQL Playground

The same pattern builds small summary reports from unrelated tables:

SELECT 'orders' AS metric, COUNT(*) AS total FROM orders
UNION ALL
SELECT 'payments', COUNT(*) FROM payments
UNION ALL
SELECT 'reviews', COUNT(*) FROM reviews;

▶ Run this query in the SQL Playground

UNION has two relatives that follow the same column rules. INTERSECT returns only rows that appear in both results; EXCEPT returns rows from the first result that are not in the second. This query finds products that have never been ordered:

SELECT id FROM products
EXCEPT
SELECT product_id FROM order_items
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

Three errors that cause most UNION problems.

SELECT first_name, city FROM users
UNION
SELECT first_name FROM employees;

SELECT first_name, city FROM users
UNION
SELECT first_name, city FROM employees;

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

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

SELECT city FROM users
UNION
SELECT city FROM employees;

SELECT city FROM users
UNION ALL
SELECT city FROM employees;

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List every distinct country or city name that appears as a customer country or an employee city, sorted alphabetically. Table: users, employees

   Show solution

   SQLite · runs in the Playground

   SELECT country AS place FROM users
   UNION
   SELECT city FROM employees
   ORDER BY place;

   ▶ Run this query in the SQL Playground

   UNION removes repeats, leaving 28 distinct names. The column is called place because names come from the first SELECT.

2. 2Easy Return the email addresses of all customers and all employees in one list, keeping every row. Table: users, employees

   Show solution

   SQLite · runs in the Playground

   SELECT email FROM users
   UNION ALL
   SELECT email FROM employees;

   ▶ Run this query in the SQL Playground

   UNION ALL keeps all 270 rows: 150 customers plus 120 employees.

3. 3Medium Show the number of cancelled orders and the number of failed payments as two labelled rows. Table: orders, payments

   Show solution

   SQLite · runs in the Playground

   SELECT 'cancelled orders' AS metric, COUNT(*) AS total
   FROM orders
   WHERE status = 'cancelled'
   UNION ALL
   SELECT 'failed payments', COUNT(*)
   FROM payments
   WHERE status = 'failed';

   ▶ Run this query in the SQL Playground

   Each SELECT produces one summary row; UNION ALL stacks them into a two-row report.

4. 4Medium Find five customers who have never written a review, using EXCEPT. Table: users, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT id FROM users
   EXCEPT
   SELECT user_id FROM reviews
   ORDER BY id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   EXCEPT keeps user ids that do not appear in reviews. A LEFT JOIN with IS NULL gives the same answer.