Skip to content
elephantoo

UNION, INTERSECT & EXCEPT

Lesson 15 of 31 12 min read

Stack result sets with UNION and UNION ALL, and find overlaps and differences in MySQL 8.0.31+.

Joins put tables side by side, adding columns. Set operations stack result sets on top of each other, adding rows. Use them to combine similar data from different tables or queries, or to find what two lists have in common.

Sample data#

SQL
CREATE TABLE customers_2025 (email VARCHAR(40) PRIMARY KEY, city VARCHAR(20));
CREATE TABLE customers_2026 (email VARCHAR(40) PRIMARY KEY, city VARCHAR(20));

INSERT INTO customers_2025 VALUES
    ('ada@x.com', 'London'), ('alan@x.com', 'Manchester'), ('linus@x.com', 'Helsinki');
INSERT INTO customers_2026 VALUES
    ('alan@x.com', 'Manchester'), ('grace@x.com', 'New York'), ('linus@x.com', 'Portland');

UNION: combine and remove duplicates#

SQL
SELECT email FROM customers_2025
UNION
SELECT email FROM customers_2026
ORDER BY email;
Output
+-------------+
| email       |
+-------------+
| ada@x.com   |
| alan@x.com  |
| grace@x.com |
| linus@x.com |
+-------------+

Alan and Linus were customers in both years, but they appear only once: UNION removes duplicate rows.

UNION ALL: keep everything#

SQL
SELECT email, 2025 AS yr FROM customers_2025
UNION ALL
SELECT email, 2026 FROM customers_2026
ORDER BY email, yr;
Output
+-------------+------+
| email       | yr   |
+-------------+------+
| ada@x.com   | 2025 |
| alan@x.com  | 2025 |
| alan@x.com  | 2026 |
| grace@x.com | 2026 |
| linus@x.com | 2025 |
| linus@x.com | 2026 |
+-------------+------+

UNION ALL skips the de-duplication step, so it's faster. Use it whenever duplicates are impossible or wanted. Adding a constant column like yr is a neat way to label where each row came from.

Duplicates are judged on whole rows. Linus moved city, so these two rows differ and both survive a plain UNION:

SQL
SELECT email, city FROM customers_2025 WHERE email = 'linus@x.com'
UNION
SELECT email, city FROM customers_2026 WHERE email = 'linus@x.com';
Output
+-------------+----------+
| email       | city     |
+-------------+----------+
| linus@x.com | Helsinki |
| linus@x.com | Portland |
+-------------+----------+

The rules#

  1. Every SELECT must return the same number of columns.
  2. Columns are matched by position, not by name. Corresponding columns should have compatible types.
  3. Column names come from the first SELECT.
  4. ORDER BY and LIMIT at the end apply to the whole result. To sort or limit one part, wrap it in parentheses:
SQL
(SELECT email FROM customers_2025 ORDER BY email LIMIT 1)
UNION ALL
(SELECT email FROM customers_2026 ORDER BY email DESC LIMIT 1);
Output
+-------------+
| email       |
+-------------+
| ada@x.com   |
| linus@x.com |
+-------------+

A practical example: one activity feed#

Set operations shine when similar events live in different tables:

SQL
CREATE TABLE logins   (user_id INT, at DATETIME);
CREATE TABLE payments (user_id INT, amount DECIMAL(8,2), at DATETIME);
INSERT INTO logins   VALUES (1, '2026-09-29 09:00:00'), (2, '2026-09-29 10:30:00');
INSERT INTO payments VALUES (1, 499.00, '2026-09-29 09:05:00');

SELECT user_id, 'login' AS event, NULL AS amount, at FROM logins
UNION ALL
SELECT user_id, 'payment', amount, at FROM payments
ORDER BY at;
Output
+---------+---------+--------+---------------------+
| user_id | event   | amount | at                  |
+---------+---------+--------+---------------------+
|       1 | login   |   NULL | 2026-09-29 09:00:00 |
|       1 | payment | 499.00 | 2026-09-29 09:05:00 |
|       2 | login   |   NULL | 2026-09-29 10:30:00 |
+---------+---------+--------+---------------------+

INTERSECT: rows in both (MySQL 8.0.31+)#

SQL
SELECT email FROM customers_2025
INTERSECT
SELECT email FROM customers_2026
ORDER BY email;
Output
+-------------+
| email       |
+-------------+
| alan@x.com  |
| linus@x.com |
+-------------+

These are the returning customers.

EXCEPT: rows in the first but not the second (MySQL 8.0.31+)#

SQL
SELECT email FROM customers_2025
EXCEPT
SELECT email FROM customers_2026;
Output
+-----------+
| email     |
+-----------+
| ada@x.com |
+-----------+

Ada bought in 2025 but not in 2026: a churned customer. Order matters with EXCEPT. Swapping the queries gives the new customers instead (Grace).

Like UNION, both INTERSECT and EXCEPT remove duplicates by default. Add ALL to keep them. When mixed without parentheses, INTERSECT is evaluated before UNION and EXCEPT.

On older versions: EXISTS instead#

Before 8.0.31 (and on some MariaDB versions), write the same logic with EXISTS and NOT EXISTS:

SQL
-- INTERSECT equivalent
SELECT a.email FROM customers_2025 a
WHERE EXISTS (SELECT 1 FROM customers_2026 b WHERE b.email = a.email);

-- EXCEPT equivalent
SELECT a.email FROM customers_2025 a
WHERE NOT EXISTS (SELECT 1 FROM customers_2026 b WHERE b.email = a.email);

These work everywhere and can use indexes on email.

Using set operations as building blocks#

A set operation can feed a derived table, a CTE, an INSERT ... SELECT or a view, just like any other query. For example, count how many customers fall into each group across both years:

SQL
WITH all_customers AS (
    SELECT email FROM customers_2025
    UNION
    SELECT email FROM customers_2026
)
SELECT
    COUNT(*) AS total_customers,
    SUM(email IN (SELECT email FROM customers_2025)
        AND email IN (SELECT email FROM customers_2026)) AS returning,
    SUM(email NOT IN (SELECT email FROM customers_2026)) AS churned,
    SUM(email NOT IN (SELECT email FROM customers_2025)) AS new_in_2026
FROM all_customers;
Output
+-----------------+-----------+---------+-------------+
| total_customers | returning | churned | new_in_2026 |
+-----------------+-----------+---------+-------------+
|               4 |         2 |       1 |           1 |
+-----------------+-----------+---------+-------------+

And copy a de-duplicated mailing list into its own table in one statement:

SQL
CREATE TABLE mailing_list (email VARCHAR(40) PRIMARY KEY);
INSERT INTO mailing_list (email)
SELECT email FROM customers_2025
UNION
SELECT email FROM customers_2026;
SELECT COUNT(*) AS subscribers FROM mailing_list;
Output
+-------------+
| subscribers |
+-------------+
|           4 |
+-------------+

With UNION ALL here, the second copy of Alan would violate the primary key, which is another reason to choose the operator deliberately.

Set operations vs joins#

QuestionTool
"Give me rows from A and rows from B in one list"UNION / UNION ALL
"Rows that appear in both lists"INTERSECT, or EXISTS
"Rows in A that are not in B"EXCEPT, or NOT EXISTS / LEFT JOIN ... IS NULL
"Combine columns of A and B for matching rows"JOIN

Common mistakes#

  • Different column counts: ERROR 1222: The used SELECT statements have a different number of columns.
  • Columns in a different order in each SELECT. MySQL matches by position, so you'll silently mix up emails and cities.
  • Using UNION when UNION ALL will do. You pay for an unnecessary sort or hash step on big results.
  • Expecting ORDER BY inside one part to survive. Without LIMIT, MySQL may ignore an inner ORDER BY. Sort the final result.

What's next#

Long queries with nested subqueries get hard to read. Next you'll name intermediate steps with CTEs and solve hierarchical problems with recursive CTEs.

Check your understanding

Quick quiz

0/3 answered

  1. 1.What is the difference between UNION and UNION ALL?

  2. 2.Where do the column names of a UNION result come from?

  3. 3.Which MySQL version first supports INTERSECT and EXCEPT?

Finished reading?

Mark this lesson complete to track your progress.