SQL UNION, INTERSECT and EXCEPT

Joins put tables side by side. Set operations stack query results on top of each other: combine two lists (UNION), keep only what both have (INTERSECT), or what one has and the other doesn't (EXCEPT). They're useful for merging data from similar tables, building reports from several queries and comparing datasets.

UNION and UNION ALL

-- Every city we know about: customer cities plus a list of new delivery cities
SELECT city FROM customers WHERE city IS NOT NULL
UNION
SELECT 'Hyderabad'
UNION
SELECT 'Pune'
ORDER BY city;

+-----------+
| city      |
+-----------+
| Bengaluru |
| Delhi     |
| Hyderabad |
| Kochi     |
| Mumbai    |
| Pune      |
+-----------+

SELECT city FROM customers WHERE city IS NOT NULL
UNION ALL
SELECT 'Pune'
ORDER BY city;

+-----------+
| city      |
+-----------+
| Bengaluru |
| Delhi     |
| Delhi     |
| Kochi     |
| Mumbai    |
| Pune      |
| Pune      |
| Pune      |
+-----------+

UNION removes duplicates (Pune appears once); UNION ALL keeps every row (Pune three times). Removing duplicates means sorting or hashing all rows, so use UNION ALL whenever duplicates are impossible or wanted – it's faster.

The rules

• Each query must return the same number of columns, with compatible types.
• Column names come from the first query.
• ORDER BY and LIMIT go once, at the very end, and apply to the combined result.

Labelling where rows came from

-- One "activity" list from two different tables
SELECT 'signup' AS event, name AS who, signup_date AS event_date
FROM customers WHERE signup_date >= '2025-06-01'
UNION ALL
SELECT 'hire', name, hire_date
FROM employees WHERE hire_date >= '2024-01-01'
ORDER BY event_date;

+--------+--------------+------------+
| event  | who          | event_date |
+--------+--------------+------------+
| hire   | Tom Mathew   | 2024-01-15 |
| hire   | Imran Shaikh | 2024-08-01 |
| signup | Neha Gupta   | 2025-06-25 |
| signup | Vikram Das   | 2025-07-30 |
+--------+--------------+------------+

A report with a total row

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status
UNION ALL
SELECT 'TOTAL', COUNT(*) FROM orders
ORDER BY status = 'TOTAL', orders DESC, status;

+-----------+--------+
| status    | orders |
+-----------+--------+
| delivered |      7 |
| pending   |      2 |
| shipped   |      2 |
| cancelled |      1 |
| TOTAL     |     12 |
+-----------+--------+

INTERSECT: rows in both

-- Customers who ordered in BOTH Aug–Sep and Oct–Nov (repeat customers across the two periods)
SELECT customer_id FROM orders WHERE order_date >= '2025-08-01' AND order_date < '2025-10-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2025-10-01' AND order_date < '2025-12-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
+-------------+

EXCEPT: rows in the first but not the second

-- Customers who ordered in Aug–Sep but NOT in Oct–Nov (customers we may be losing)
SELECT customer_id FROM orders WHERE order_date >= '2025-08-01' AND order_date < '2025-10-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2025-10-01' AND order_date < '2025-12-01';

+-------------+
| customer_id |
+-------------+
|           5 |
+-------------+

-- The same EXCEPT written with NOT EXISTS (works on every version)
SELECT DISTINCT a.customer_id
FROM orders a
WHERE a.order_date >= '2025-08-01' AND a.order_date < '2025-10-01'
  AND NOT EXISTS (SELECT 1 FROM orders b
                  WHERE b.customer_id = a.customer_id
                    AND b.order_date >= '2025-10-01' AND b.order_date < '2025-12-01');

+-------------+
| customer_id |
+-------------+
|           5 |
+-------------+

Practice

Summary

Interview practice: SQL tricky output questions.