Set Operations: UNION, INTERSECT, EXCEPT

Set operations combine the results of two queries vertically — one result's rows on top of another's.

UNION and UNION ALL

SELECT name, 'customer' AS type FROM customers WHERE city = 'London'
UNION ALL
SELECT name, 'employee' AS type FROM employees WHERE department = 'Sales'
ORDER BY type, name;

+--------------+----------+
|     name     |   type   |
+--------------+----------+
| Ada Lovelace | customer |
| Alan Turing  | customer |
| Chen         | employee |
| Lucia        | employee |
+--------------+----------+

• UNION ALL keeps every row from both queries.
• UNION also removes duplicate rows — which costs extra work, so use UNION ALL unless you need de-duplication.
• Both queries must return the same number of columns, with compatible types. Column names come from the first query.
• One ORDER BY at the end sorts the combined result.

INTERSECT and EXCEPT

SELECT customer_id FROM orders WHERE status = 'delivered'
INTERSECT
SELECT customer_id FROM orders WHERE status = 'pending';

+-------------+
| customer_id |
+-------------+
|           1 |
+-------------+

INTERSECT keeps rows in both results: customers with both a delivered and a pending order.

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

+----+
| id |
+----+
|  6 |
+----+

EXCEPT keeps rows in the first result but not the second: customers with no orders. (Oracle calls it MINUS. MySQL supports INTERSECT and EXCEPT from version 8.0.31.)

When to use them

• UNION ALL — combining similar data from different tables, such as yearly archive tables, or building a single activity feed from several sources.
• INTERSECT / EXCEPT — quick comparisons of two lists, such as checking which IDs are missing after an import.