Set Operations: UNION, INTERSECT and EXCEPT
Stack, overlap and subtract whole result sets - not columns, but entire rows
Big Idea: A set operation takes the rows produced by two
SELECT queries and combines them vertically. A JOIN glues tables side by side to make wider rows; a set operation stacks result sets on top of each other to make one longer list. UNION merges them, INTERSECT keeps only rows present in both, and EXCEPT keeps rows in the first that are missing from the second.
By the end you will be able to:
- combine two result sets with
UNIONand know when to reach forUNION ALLinstead, - find rows common to two queries with
INTERSECT, - subtract one result set from another with
EXCEPT, - satisfy the column compatibility rules that every set operation demands,
- reproduce
INTERSECTandEXCEPTon older MySQL that lacks them (before 8.0.31).
1. Our example database
Every query below runs against two small tables. A company keeps a list of its employees and, separately, a list of its customers. Both records happen to store a person's name and their city.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE customers (
cust_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
INSERT INTO employees (emp_id, name, city) VALUES
(1, 'Alice', 'London'),
(2, 'Bob', 'Paris'),
(3, 'Carol', 'London'),
(4, 'David', 'Berlin');
INSERT INTO customers (cust_id, name, city) VALUES
(101, 'Emma', 'Paris'),
(102, 'Frank', 'London'),
(103, 'Grace', 'Paris'),
(104, 'Alice', 'Madrid');Keep these eight rows in mind. Notice the cities: employees live in London, Paris and Berlin; customers live in Paris, London and Madrid. Those overlaps and differences are exactly what set operations let us describe.