Chapter 21 of 35

UNION & Set Operations

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 result

A 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

UNION

A ∪ B

Rows in A or B (or both), duplicates removed

UNION ALL

-

All rows of A followed by all rows of B, duplicates kept

INTERSECT

A ∩ B

Rows that appear in both A and B

EXCEPT

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 rows

Two 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 = NULL is UNKNOWN, but for removing duplicates (in DISTINCT, UNION, INTERSECT and EXCEPT) 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:

  1. Same number of columns. This is the hard rule; MySQL refuses otherwise.

  2. 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.

  3. 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 columns

Rule 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 BY inside 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 an ORDER BY at 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}                                                                     1

The 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              0
SELECT 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:

  • INTERSECT binds tighter than UNION and EXCEPT (like × before + in arithmetic).

  • UNION and EXCEPT have 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

A INTERSECT B

SELECT DISTINCT ... INNER JOIN on all columns, or WHERE col IN (subquery), or EXISTS

A EXCEPT B

LEFT JOIN ... WHERE b.col IS NULL, or NOT EXISTS

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 NULL or a constant as filler if needed)

Columns in a different order

Runs, but values land under the wrong heading

Line up columns by meaning; matching is by position

Using UNION when duplicates are impossible

Correct but slower (useless duplicate check)

Use UNION ALL

Using UNION ALL when parts overlap

Same row counted twice

Use UNION, or one query with OR

ORDER BY using an alias from the second query

ERROR 1054 Unknown column

Use the first query's names

ORDER BY/LIMIT on one part without parentheses

Syntax error, or applies to the whole union

Wrap that part in ( )

Mixing UNION and INTERSECT without brackets

INTERSECT runs first; unexpected result

Add parentheses

Writing MINUS

Syntax error in MySQL

Use EXCEPT

Replacing INTERSECT with a JOIN on nullable columns

NULL rows silently lost

Use <=> or filter NULLs consciously


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 ALL keeps 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/LIMIT applies to the whole result; parenthesise a part to sort or limit it alone.

  • INTERSECT binds tighter than UNION/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.