Joins, which we studied in the last few chapters, combine tables side by side: they add columns from one table next to columns from another. Sometimes you need something different. You want to stack the results of two queries one on top of the other: "all cities where we have either an employee or a customer", "people from Mumbai, both staff and customers, in one list", or "customers who have never ordered".
That is the job of the set operations: UNION, UNION ALL, INTERSECT and EXCEPT. They come straight from set theory in mathematics, which you may remember as ∪, ∩ and − from school. In this chapter we will see how each one works, the rules both queries must follow, how duplicates and NULLs are treated, and the mistakes that cost marks in exams and interviews.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output. INTERSECT and EXCEPT need MySQL 8.0.31 or later; on older versions only UNION exists (we will see the workarounds).
Joins vs Set Operations
Both combine data from two sources, but in completely different directions:
JOIN: combine COLUMNS (horizontal) SET OPERATION: combine ROWS (vertical)
Query A Query B Query A result
+---+---+ +---+---+ +-----------+
| a | b | | c | d | | row 1 |
+---+---+ +---+---+ | row 2 |
\ / +-----------+
\ / + (UNION / INTERSECT / EXCEPT)
+---+---+---+---+ +-----------+
| a | b | c | d | wider rows | row 3 | Query B result
+---+---+---+---+ | row 4 |
+-----------+
= one taller resultA set operation takes the results of two SELECT queries that have the same number of columns and combines their rows into a single result.
Operation | Math symbol | Returns |
|---|---|---|
| A ∪ B | Rows in A or B (or both), duplicates removed |
| - | All rows of A followed by all rows of B, duplicates kept |
| A ∩ B | Rows that appear in both A and B |
| A − B | Rows in A that do not appear in B |
Other databases use MINUS for EXCEPT (Oracle). MySQL does not accept MINUS; use EXCEPT.
UNION: Combine and Remove Duplicates
Which cities do we have any presence in, either through an employee or a customer?
SELECT city FROM employees
UNION
SELECT city FROM customers;+-----------+
| city |
+-----------+
| Bengaluru |
| Chennai |
| Mumbai |
| Kolkata |
| Delhi |
| Kochi |
| Hyderabad |
| NULL |
| Pune |
+-----------+
9 rows in set (0.00 sec)The employees table has 10 city values and customers has 5, so 15 values in total. But UNION returned only 9. Why? Because UNION behaves like a mathematical set: each distinct row appears once. Bengaluru appears three times across the two tables (Aarav, Rohan, Kavya) but only once in the result.
Here is what happens internally:
Step 1: run query A Step 2: run query B
Bengaluru, Chennai, Bengaluru, Delhi, Bengaluru, Pune,
Mumbai, Mumbai, Kolkata, Delhi, Mumbai, NULL
Kochi, Hyderabad, NULL
Step 3: append B below A -> 15 rows
Step 4: remove duplicate rows (like DISTINCT) -> 9 rowsTwo details are worth noticing:
NULL appears once. Ishita (employee) and Siddharth (customer) both have a NULL city, yet there is a single NULL row. In the NULL Values chapter we saw that
NULL = NULLis UNKNOWN, but for removing duplicates (inDISTINCT,UNION,INTERSECTandEXCEPT) SQL treats two NULLs as the same value.The order is not guaranteed. The rows above happen to come out in "first seen" order, but SQL promises no order without
ORDER BY. We will sort unions shortly.
You can also write UNION DISTINCT. It means exactly the same as plain UNION; the word DISTINCT just makes the intent explicit.
UNION ALL: Keep Everything
SELECT city FROM employees
UNION ALL
SELECT city FROM customers;+-----------+
| city |
+-----------+
| Bengaluru |
| Chennai |
| Bengaluru |
| Mumbai |
| Mumbai |
| Kolkata |
| Delhi |
| Kochi |
| Hyderabad |
| NULL |
| Delhi |
| Bengaluru |
| Pune |
| Mumbai |
| NULL |
+-----------+
15 rows in set (0.00 sec)All 15 values, duplicates included: the first 10 rows come from employees, the last 5 from customers.
UNION vs UNION ALL: which one to use?
Removing duplicates is not free. MySQL has to put the combined rows into a temporary table with a unique check (or sort them) to find duplicates. UNION ALL simply streams rows from the first query and then the second.
UNION | UNION ALL | |
|---|---|---|
Duplicates | Removed | Kept |
Extra work | Duplicate elimination (temporary table / sort) | None, just appends |
Speed | Slower on large results | Faster |
Use when | You really need distinct rows | Duplicates are impossible or you want them (counting, reports) |
Rule of thumb: default to UNION ALL, and switch to UNION only when duplicates would be wrong. If the two parts can never overlap (for example, one reads employees and the other customers and you add a type label), UNION would do the duplicate check for nothing.
Watch how the choice changes the answer when the two parts do overlap. Engineering employees, plus everyone earning over 90,000:
SELECT name, salary FROM employees WHERE dept_id = 10
UNION
SELECT name, salary FROM employees WHERE salary > 90000;+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)With UNION ALL the same query returns 7 rows, because Aarav, Priya and Rohan satisfy both conditions and are listed twice:
+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
7 rows in set (0.00 sec)(When both parts read the same table, like here, a single query with WHERE dept_id = 10 OR salary > 90000 is simpler and returns the same 4 rows. Set operations shine when the parts come from different tables.)
The Rules for Combining Queries
Since the rows are stacked into one result, both queries must "fit" together:
Same number of columns. This is the hard rule; MySQL refuses otherwise.
Compatible types, column by column. Columns are matched by position, not by name. The 1st column of query A goes with the 1st column of query B, and so on.
Column names come from the first query. Aliases in later queries are ignored.
Rule 1: different column counts give an error
SELECT name FROM employees
UNION
SELECT name, city FROM customers;ERROR 1222 (21000): The used SELECT statements have a different number of columnsRule 2: columns are matched by position
SQL does not check that the columns "mean" the same thing. This query is legal, even though it stacks numbers on top of names:
SELECT emp_id AS id_or_name FROM employees WHERE emp_id <= 2
UNION
SELECT name FROM customers WHERE customer_id = 101;+-------------+
| id_or_name |
+-------------+
| 1 |
| 2 |
| Rahul Verma |
+-------------+
3 rows in set (0.00 sec)MySQL picked a result type that can hold both an INT and a VARCHAR, which is a string. Notice that 1 and 2 are now left-aligned: they have become text. MySQL is lenient here; stricter databases such as PostgreSQL reject mixing integers and text. Either way, it is almost always a bug, so always line up columns that mean the same thing, in the same order.
A classic version of this bug is swapping two columns:
-- Wrong: the second query lists city before name
SELECT name, city FROM employees WHERE emp_id = 5
UNION
SELECT city, name FROM customers WHERE customer_id = 104;+--------------+--------------+
| name | city |
+--------------+--------------+
| Vikram Singh | Mumbai |
| Mumbai | Neha Agarwal |
+--------------+--------------+
2 rows in set (0.00 sec)It runs without error and silently puts a customer's city under the name heading.
Rule 3: names come from the first SELECT
SELECT name AS person FROM employees WHERE dept_id = 30
UNION
SELECT name AS customer_name FROM customers WHERE city = 'Pune';+--------------+
| person |
+--------------+
| Karan Patel |
| Meera Nair |
| Aditya Joshi |
+--------------+
3 rows in set (0.00 sec)The heading is person; the alias customer_name was ignored. That also means you cannot sort by it:
SELECT name AS person FROM employees WHERE dept_id = 30
UNION
SELECT name FROM customers WHERE city = 'Pune'
ORDER BY customer_name;ERROR 1054 (42S22): Unknown column 'customer_name' in 'order clause'Labelling Where Each Row Came From
When you stack rows from different tables, the reader often needs to know which table each row came from. Add a constant column:
SELECT name, city, 'Employee' AS person_type
FROM employees WHERE city = 'Mumbai'
UNION ALL
SELECT name, city, 'Customer'
FROM customers WHERE city = 'Mumbai';+--------------+--------+-------------+
| name | city | person_type |
+--------------+--------+-------------+
| Sneha Kapoor | Mumbai | Employee |
| Vikram Singh | Mumbai | Employee |
| Neha Agarwal | Mumbai | Customer |
+--------------+--------+-------------+
3 rows in set (0.00 sec)This is a very common pattern in reports. Because the label makes every row from the two parts different, UNION ALL is the right choice: UNION could never find a duplicate and would only waste time looking.
Sorting and Limiting a Union
An ORDER BY (and LIMIT) written after the last query applies to the whole combined result, not just to the last query:
SELECT name, city FROM employees WHERE city IN ('Delhi', 'Pune')
UNION
SELECT name, city FROM customers WHERE city IN ('Delhi', 'Pune')
ORDER BY city, name;+--------------+-------+
| name | city |
+--------------+-------+
| Karan Patel | Delhi |
| Rahul Verma | Delhi |
| Aditya Joshi | Pune |
+--------------+-------+
3 rows in set (0.00 sec)Use the column names (or aliases) of the first query in that ORDER BY. Here the first query's column is city, so ORDER BY city works even though the second query reads departments.location:
SELECT city FROM employees WHERE city IS NOT NULL
UNION
SELECT location FROM departments
ORDER BY city;+-----------+
| city |
+-----------+
| Bengaluru |
| Chennai |
| Delhi |
| Hyderabad |
| Kochi |
| Kolkata |
| Mumbai |
| Pune |
+-----------+
8 rows in set (0.00 sec)Sorting each part separately
What if you want the top 2 earners and the bottom 2 earners in one list? Each part needs its own ORDER BY ... LIMIT. Wrap each part in parentheses:
(SELECT name, salary, 'Top' AS band
FROM employees ORDER BY salary DESC LIMIT 2)
UNION ALL
(SELECT name, salary, 'Bottom'
FROM employees ORDER BY salary ASC LIMIT 2);+--------------+-----------+--------+
| name | salary | band |
+--------------+-----------+--------+
| Aarav Sharma | 150000.00 | Top |
| Sneha Kapoor | 110000.00 | Top |
| Meera Nair | 48000.00 | Bottom |
| Ishita Gupta | 55000.00 | Bottom |
+--------------+-----------+--------+
4 rows in set (0.00 sec)Without the parentheses, MySQL rejects the query with ERROR 1064 (42000): You have an error in your SQL syntax, because only the final ORDER BY/LIMIT of a union is allowed outside brackets.
Careful: an
ORDER BYinside a parenthesised part without a LIMIT has no lasting effect. MySQL may ignore it, because the union result is an unordered set anyway. If you want the final output in a particular order, put anORDER BYat the very end.
INTERSECT: Rows Present in Both
Which cities have both an employee and a customer?
SELECT city FROM employees
INTERSECT
SELECT city FROM customers;+-----------+
| city |
+-----------+
| Bengaluru |
| Mumbai |
| Delhi |
| NULL |
+-----------+
4 rows in set (0.00 sec)Bengaluru, Mumbai and Delhi appear on both sides. NULL is also in the result: both tables contain a NULL city, and for set operations NULLs are treated as equal. Whether "unknown city" really counts as a common city is a business question; add WHERE city IS NOT NULL to both parts if you do not want it.
A practical example: customers who have placed at least one order.
SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM orders;+-------------+
| customer_id |
+-------------+
| 101 |
| 102 |
| 103 |
| 104 |
+-------------+
4 rows in set (0.00 sec)EXCEPT: Rows in the First but Not the Second
Cities where we have employees but no customers:
SELECT city FROM employees
EXCEPT
SELECT city FROM customers;+-----------+
| city |
+-----------+
| Chennai |
| Kolkata |
| Kochi |
| Hyderabad |
+-----------+
4 rows in set (0.00 sec)Unlike UNION and INTERSECT, EXCEPT is not symmetric. Swap the two queries and you answer a different question, "cities with customers but no employees":
SELECT city FROM customers
EXCEPT
SELECT city FROM employees;+------+
| city |
+------+
| Pune |
+------+
1 row in set (0.00 sec)A = employee cities {Bengaluru, Chennai, Mumbai, Kolkata, Delhi, Kochi, Hyderabad, NULL}
B = customer cities {Delhi, Bengaluru, Pune, Mumbai, NULL}
A ∪ B = {Bengaluru, Chennai, Mumbai, Kolkata, Delhi, Kochi, Hyderabad, NULL, Pune} 9
A ∩ B = {Bengaluru, Mumbai, Delhi, NULL} 4
A − B = {Chennai, Kolkata, Kochi, Hyderabad} 4
B − A = {Pune} 1The classic use of EXCEPT is finding "missing" things. Customers who have never ordered:
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;+-------------+
| customer_id |
+-------------+
| 105 |
+-------------+
1 row in set (0.00 sec)Siddharth Rao (105) has no orders. Notice that this works correctly even though orders contains a guest order with a NULL customer_id. Remember the NOT IN trap from the NULL Values chapter: WHERE customer_id NOT IN (SELECT customer_id FROM orders) would return no rows at all here because of that NULL. EXCEPT compares rows with "NULLs are equal" logic, so it does not have this problem.
INTERSECT ALL and EXCEPT ALL
Just as UNION ALL keeps duplicates, INTERSECT ALL and EXCEPT ALL work with counts (multisets, also called bags) instead of distinct values:
INTERSECT ALL: a value that occurs m times in A and n times in B appears min(m, n) times.EXCEPT ALL: it appears max(m − n, 0) times.
City in employees (m) in customers (n) INTERSECT ALL EXCEPT ALL
Bengaluru 2 1 1 1
Mumbai 2 1 1 1
Delhi 1 1 1 0
NULL 1 1 1 0
Chennai 1 0 0 1
Kolkata 1 0 0 1
Kochi 1 0 0 1
Hyderabad 1 0 0 1
Pune 0 1 0 0SELECT city FROM employees
EXCEPT ALL
SELECT city FROM customers;+-----------+
| city |
+-----------+
| Bengaluru |
| Chennai |
| Mumbai |
| Kolkata |
| Kochi |
| Hyderabad |
+-----------+
6 rows in set (0.00 sec)Bengaluru and Mumbai survive once each (2 − 1 = 1), Delhi and NULL disappear (1 − 1 = 0). INTERSECT ALL on the same data returns Bengaluru, Mumbai, Delhi and NULL, once each, because every common city has a minimum count of 1. These variants are rarely used, but they are a favourite trick question.
Combining More Than Two Queries: Precedence
You can chain any number of set operations. Without parentheses, MySQL follows the SQL standard:
INTERSECTbinds tighter thanUNIONandEXCEPT(like × before + in arithmetic).UNIONandEXCEPThave equal priority and are evaluated left to right.
SELECT location FROM departments
UNION
SELECT city FROM customers
INTERSECT
SELECT city FROM employees;+-----------+
| location |
+-----------+
| Bengaluru |
| Mumbai |
| Delhi |
| Pune |
| Hyderabad |
| NULL |
+-----------+
6 rows in set (0.00 sec)This was read as departments ∪ (customers ∩ employees). Pune is in the result because it is a department location, even though no employee lives there. Add parentheses to get the other meaning:
(SELECT location FROM departments
UNION
SELECT city FROM customers)
INTERSECT
SELECT city FROM employees;+-----------+
| location |
+-----------+
| Bengaluru |
| Mumbai |
| Delhi |
| Hyderabad |
| NULL |
+-----------+
5 rows in set (0.00 sec)Now Pune is gone, because no employee lives in Pune. Same three queries, different answers. Whenever you mix operators, use parentheses, both for correctness and so the next reader does not have to remember precedence rules.
A Practical Pattern: Adding a Total Row
Set operations can also glue a summary row under a grouped result. (Grouping was covered in the GROUP BY & HAVING chapter.)
SELECT status, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY status
UNION ALL
SELECT 'ALL', COUNT(*), SUM(amount)
FROM orders;+-----------+--------+----------+
| status | orders | total |
+-----------+--------+----------+
| Delivered | 4 | 17700.00 |
| Cancelled | 1 | 1200.00 |
| Shipped | 1 | 950.00 |
| Pending | 2 | 6400.00 |
| ALL | 8 | 26250.00 |
+-----------+--------+----------+
5 rows in set (0.00 sec)MySQL also offers GROUP BY ... WITH ROLLUP for exactly this, but the UNION ALL version works on every database and lets you label the total row however you like.
Doing Without INTERSECT and EXCEPT
MySQL versions before 8.0.31, and many older interview questions, assume only UNION exists. The same results can be produced with tools you already know:
Set operation | Alternative |
|---|---|
|
|
|
|
For example, cities with both an employee and a customer using a join:
SELECT DISTINCT e.city
FROM employees e
JOIN customers c ON e.city = c.city;+-----------+
| city |
+-----------+
| Bengaluru |
| Mumbai |
| Delhi |
+-----------+
3 rows in set (0.01 sec)Compare it with the INTERSECT result earlier: 3 rows instead of 4. The NULL row is missing, because a join condition uses =, and NULL = NULL is UNKNOWN. The rewrite is "the same" only when the columns cannot be NULL (or when you do not want NULL matches anyway). You could match NULLs too with the null-safe operator, ON e.city <=> c.city. The IN and EXISTS forms are explained in the next two chapters, Subqueries and Correlated Subqueries & EXISTS.
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Different number of columns | ERROR 1222 | Make both SELECT lists the same length (add |
Columns in a different order | Runs, but values land under the wrong heading | Line up columns by meaning; matching is by position |
Using | Correct but slower (useless duplicate check) | Use |
Using | Same row counted twice | Use |
| ERROR 1054 Unknown column | Use the first query's names |
| Syntax error, or applies to the whole union | Wrap that part in |
Mixing | INTERSECT runs first; unexpected result | Add parentheses |
Writing | Syntax error in MySQL | Use |
Replacing INTERSECT with a JOIN on nullable columns | NULL rows silently lost | Use |
Interview Questions
What is the difference between UNION and UNION ALL?
UNION removes duplicate rows from the combined result; UNION ALL keeps all rows. UNION ALL is faster because it skips duplicate elimination, so prefer it when duplicates cannot occur or are wanted.
What is the difference between a JOIN and a UNION?
A JOIN combines columns from related tables into wider rows, matched by a join condition. A UNION stacks the rows of two queries with the same number of columns into a taller result. No relationship between the tables is needed for UNION.
What conditions must two queries meet to be combined with UNION?
Same number of columns, and compatible data types in corresponding positions. Columns are matched by position, and the result's column names are taken from the first query.
How are NULLs treated in UNION, INTERSECT and EXCEPT?
As equal to each other. Two NULL rows are considered duplicates, so UNION keeps one NULL, and INTERSECT returns NULL if both sides contain it. This differs from = comparisons, where NULL = NULL is UNKNOWN.
Does MySQL support INTERSECT and EXCEPT? What about MINUS?
Yes, from MySQL 8.0.31 (including ALL variants). MINUS is Oracle's name for EXCEPT and is not supported by MySQL.
Which has higher precedence, UNION or INTERSECT?
INTERSECT. A UNION B INTERSECT C means A UNION (B INTERSECT C). UNION and EXCEPT have equal precedence and are evaluated left to right.
How do you sort only one part of a UNION?
Put that SELECT in parentheses with its own ORDER BY and LIMIT. An ORDER BY after the last SELECT sorts the entire combined result. An inner ORDER BY without LIMIT may be ignored.
Find customers who have never placed an order, without a JOIN.
SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders; It is also NULL-safe, unlike NOT IN when orders.customer_id contains NULL.
What do INTERSECT ALL and EXCEPT ALL return?
They work on counts. If a row occurs m times in A and n times in B, INTERSECT ALL returns it min(m, n) times and EXCEPT ALL returns it max(m − n, 0) times.
Summary
Set operations combine the rows of two queries; joins combine columns.
UNION= A ∪ B without duplicates;UNION ALLkeeps duplicates and is faster.INTERSECT= rows in both;EXCEPT= rows in the first but not the second (order matters). Both need MySQL 8.0.31+.Both queries need the same number of columns with compatible types; matching is by position and names come from the first query.
For duplicate removal, NULLs count as equal.
A final
ORDER BY/LIMITapplies to the whole result; parenthesise a part to sort or limit it alone.INTERSECTbinds tighter thanUNION/EXCEPT; use parentheses when mixing.
Several of the workarounds above used a query inside another query. In the next chapter, Subqueries, we will learn this powerful idea properly.