UNION, INTERSECT and EXCEPT

Joins put tables side by side: more columns. Set operations put query results on top of each other, or compare them: more (or fewer) rows with the same columns. UNION stacks two results, INTERSECT keeps the rows that appear in both, EXCEPT keeps the rows of the first that are not in the second. They come straight from the set maths you met in Maths for AI: union, intersection, difference.

They answer some very practical data questions in one line: which customers came back next quarter (retention), which ones did not (churn labels for a model), and — one of the most important checks in machine learning — whether any test examples also appear in the training data.

What you will learn

• UNION vs UNION ALL, and when each is right
• INTERSECT and EXCEPT (MINUS in Oracle; MySQL since 8.0.31)
• The rules: same number of columns, compatible types, names from the first query
• ORDER BY and LIMIT on a combined result
• UNION vs JOIN: stacking rows vs widening rows

Stacking vs widening

Take two small results: customers who ordered in the first quarter (January–March) and in the second (April–June).

A join would do something else entirely: it matches rows and puts their columns side by side. A set operation matches whole rows and keeps the same columns:

JOIN  (widen):   [order_id | customer_id]  +  [customer_id | name]
                 →  [order_id | customer_id | name]        same rows, more columns

UNION (stack):   [customer_id]   (Q1 rows)
                 [customer_id]   (Q2 rows)
                 →  [customer_id]                          same columns, more rows

UNION and UNION ALL

SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
UNION
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
+-------------+

The two queries return 8 and 6 rows (one per order), yet the result has 7. UNION removes duplicate rows — across both inputs and within each. UNION ALL keeps everything:

SELECT COUNT(*) AS union_all_rows
FROM (SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
      UNION ALL
      SELECT customer_id FROM orders WHERE order_date >= '2026-04-01') AS t;

+----------------+
| union_all_rows |
+----------------+
|             14 |
+----------------+

Default to UNION ALL and choose UNION on purpose. Using UNION to combine two months of sales silently merges two identical sales into one, and the totals come out low.

Combining different tables into one long table

Set operations do not need the same table on both sides, only the same shape. Here orders and payments become one event timeline for order 6, with a label column saying where each row came from:

SELECT order_id, order_date AS event_date, 'order placed' AS event, NULL AS amount
FROM orders
WHERE order_id = 6
UNION ALL
SELECT order_id, paid_on, 'payment (' || method || ')', amount
FROM payments
WHERE order_id = 6
ORDER BY event_date, event;

+----------+------------+-----------------+--------+
| order_id | event_date | event           | amount |
+----------+------------+-----------------+--------+
|        6 | 2026-03-08 | order placed    | NULL   |
|        6 | 2026-03-08 | payment (bkash) |   5000 |
|        6 | 2026-03-10 | payment (card)  |  10000 |
+----------+------------+-----------------+--------+

• Column names come from the first query (event_date, event); the second query's names are ignored.
• NULL AS amount fills a column the first table does not have, so both sides have four columns.
• The constant label is how you keep track of the source after stacking. This "long" event table is a common input for sequence features and for user-activity logs. (In pandas the same is pd.concat; UNION is concat followed by drop_duplicates.)

INTERSECT and EXCEPT

Customers who ordered in both quarters:

SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+

And those who ordered in Q1 but not in Q2. The EXCEPT result is a list of ids, so it can feed an IN subquery to fetch the names:

SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
    EXCEPT
    SELECT customer_id FROM orders WHERE order_date >= '2026-04-01')
ORDER BY c.customer_id;

+-------------+-----------------+
| customer_id | name            |
+-------------+-----------------+
|           4 | Rafiq Islam     |
|           5 | Sadia Chowdhury |
+-------------+-----------------+

Order matters for EXCEPT: A EXCEPT B is not B EXCEPT A (the second gives the new customers 6 and 7). This is the raw material of a churn label: "active last period, inactive this period". Like UNION, INTERSECT and EXCEPT return distinct rows.

Dialects

SQLite and PostgreSQL have long supported INTERSECT and EXCEPT. MySQL added them in 8.0.31 (2022); on older MySQL you write IN / NOT EXISTS instead. Oracle traditionally calls EXCEPT MINUS (version 21c accepts both). On MySQL 8.0.31 or newer the same queries work unchanged:

-- MySQL
SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;
SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
+-------------+
| customer_id |
+-------------+
|           4 |
|           5 |
+-------------+

PostgreSQL and MySQL also offer INTERSECT ALL and EXCEPT ALL, which count duplicates instead of removing them; SQLite has only the plain forms.

NULLs count as equal here

In a WHERE, NULL = NULL is not true. Set operations, like DISTINCT and GROUP BY, treat two NULLs as the same value:

SELECT NULL AS x INTERSECT SELECT NULL;

+------+
| x    |
+------+
| NULL |
+------+

That makes EXCEPT safe where NOT IN was not. On the previous page, NOT IN found no empty category because of the Gift Card's NULL; EXCEPT finds it:

SELECT category_id FROM categories
EXCEPT
SELECT category_id FROM products;

+-------------+
| category_id |
+-------------+
|           5 |
+-------------+

An ML check: is the test set leaking into training?

If an example from your test set also sits in the training set, the model has seen the answer and your test score is too optimistic. With two tables of prompts, INTERSECT is the check:

CREATE TABLE train_prompts (prompt TEXT NOT NULL);
CREATE TABLE test_prompts (prompt TEXT NOT NULL);
INSERT INTO train_prompts (prompt) VALUES
    ('How do I reset my password?'),
    ('Can I pay with bKash?'),
    ('How do I cancel my order?'),
    ('Do you deliver to Sylhet?');
INSERT INTO test_prompts (prompt) VALUES
    ('Can I pay with bKash?'),
    ('Where is my parcel?'),
    ('how do i reset my password?');
SELECT prompt FROM test_prompts
INTERSECT
SELECT prompt FROM train_prompts;

+-----------------------+
| prompt                |
+-----------------------+
| Can I pay with bKash? |
+-----------------------+

One leak found — but set operations compare values exactly, and the lower-case password question is the same question. Normalise both sides the same way before comparing:

SELECT LOWER(TRIM(prompt)) AS leaked FROM test_prompts
INTERSECT
SELECT LOWER(TRIM(prompt)) FROM train_prompts
ORDER BY leaked;

+-----------------------------+
| leaked                      |
+-----------------------------+
| can i pay with bkash?       |
| how do i reset my password? |
+-----------------------------+

Two of the three test prompts were in training. EXCEPT gives the clean test set (only "where is my parcel?" survives). Real deduplication goes further — near-duplicates found with embeddings — but this exact-match check is cheap and catches more than people expect.

The rules

• Same number of columns in every query, matched by position, not by name.
• Compatible types in each position. PostgreSQL is strict, MySQL converts, SQLite allows anything (see the mistakes below).
• Names come from the first query. Put your aliases there.
• One ORDER BY, at the very end, sorting the whole combined result, using the output column names (or positions). LIMIT likewise applies to the whole result.
• Precedence: INTERSECT binds tighter than UNION and EXCEPT in PostgreSQL and MySQL; SQLite simply goes left to right. With three or more parts, use derived tables to make the order explicit.

Sorting or limiting one part

The cheapest and the most expensive product in one result. An ORDER BY … LIMIT inside one part is not allowed in SQLite:

SELECT name, price FROM products ORDER BY price DESC LIMIT 1
UNION ALL
SELECT name, price FROM products ORDER BY price LIMIT 1;

Error: ORDER BY clause should come after UNION ALL not before

Wrap each part in a derived table, which works in all three dialects:

SELECT * FROM (SELECT name, price FROM products ORDER BY price DESC LIMIT 1) AS top_price
UNION ALL
SELECT * FROM (SELECT name, price FROM products ORDER BY price LIMIT 1) AS bottom_price;

+-----------------+-------+
| name            | price |
+-----------------+-------+
| 27-inch Monitor | 18500 |
| Wireless Mouse  |   900 |
+-----------------+-------+

PostgreSQL and MySQL also accept each part in plain brackets:

-- PostgreSQL
(SELECT name, price FROM products ORDER BY price DESC LIMIT 1)
UNION ALL
(SELECT name, price FROM products ORDER BY price LIMIT 1);

+-----------------+-------+
| name            | price |
+-----------------+-------+
| 27-inch Monitor | 18500 |
| Wireless Mouse  |   900 |
+-----------------+-------+

Common mistakes

• Different numbers of columns.
  SQLCopy

  SELECT name, city FROM customers
  UNION
  SELECT name FROM employees;

  OutputCopy

  Error: SELECTs to the left and right of UNION do not have the same number of result columns

  Fix: add a placeholder, e.g. SELECT name, NULL FROM employees, or select fewer columns on the left.
• Columns in the wrong order. Positions are matched, not names. SQLite happily stacks a name under an id:
  SQLCopy

  SELECT customer_id, name FROM customers WHERE customer_id = 1
  UNION ALL
  SELECT name, employee_id FROM employees WHERE employee_id = 1;

  OutputCopy

  +-----------------+--------------+
  | customer_id     | name         |
  +-----------------+--------------+
  |               1 | Nadia Rahman |
  | Ayesha Siddiqua |            1 |
  +-----------------+--------------+

  PostgreSQL catches it, and MySQL turns both columns into text without complaint:
  SQLCopy

  -- PostgreSQL
  SELECT customer_id, name FROM customers WHERE customer_id = 1
  UNION ALL
  SELECT name, employee_id FROM employees WHERE employee_id = 1;

  OutputCopy

  Error: UNION types integer and character varying cannot be matched

  Fix: list the columns in the same order on both sides, and read the result's first rows.
• UNION where you meant UNION ALL. Two identical sale rows from two sources become one. Fix: UNION ALL for records that must all count.
• Sorting by an expression. ORDER BY CASE WHEN label = 'TOTAL' … after a UNION fails in SQLite ("1st ORDER BY term does not match any column in the result set") and PostgreSQL. Fix: add a sort column to every part (0 AS is_total / 1) and order by it — see the hard exercise.
• Using UNION to add columns. If you want customer names next to their orders, that is a join, not a union.

Try it yourself

1. Easy: With EXCEPT, list the ids of products that were never sold.
2. Medium: Build an "at risk" list with one UNION: customers who have a cancelled order, plus customers who have never ordered. Show customer_id and name, ordered by customer_id.
3. Hard: Make a payment-method report with one row per method (label, number of payments, total amount) and a final ALL METHODS row, methods sorted by total from high to low and the summary row last.

Answers

-- 1
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM order_items
ORDER BY product_id;

-- 2
SELECT o.customer_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'cancelled'
UNION
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY customer_id;

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           4 | Rafiq Islam |
|           8 | Karim Uddin |
+-------------+-------------+

-- 3
SELECT method AS label, COUNT(*) AS payments, SUM(amount) AS total, 0 AS is_total
FROM payments
GROUP BY method
UNION ALL
SELECT 'ALL METHODS', COUNT(*), SUM(amount), 1
FROM payments
ORDER BY is_total, total DESC;

+-------------+----------+-------+----------+
| label       | payments | total | is_total |
+-------------+----------+-------+----------+
| card        |        6 | 44950 |        0 |
| bkash       |        5 | 18000 |        0 |
| cash        |        2 |  7100 |        0 |
| ALL METHODS |       13 | 70050 |        1 |
+-------------+----------+-------+----------+

Summary

• Set operations combine whole results row-wise: UNION (all rows, distinct), UNION ALL (all rows, duplicates kept), INTERSECT (in both), EXCEPT (in the first only).
• Prefer UNION ALL unless you really want a distinct list; it is also faster.
• Every part needs the same number of columns, matched by position, with compatible types; names come from the first part.
• One ORDER BY/LIMIT at the end applies to the whole result; to limit one part, wrap it in a derived table.
• MySQL has INTERSECT/EXCEPT only from 8.0.31; Oracle's traditional name for EXCEPT is MINUS. Set operations treat NULLs as equal.

Next: Project: A Sales Analytics Report for BitByte Shop puts everything from this chapter together — joins, aggregates, subqueries and set operations — into a complete report run from Python and exported for a chart.