Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 75 ยท Querying with MySQL

UNION, INTERSECT and EXCEPT

Combine query results in MySQL: UNION vs UNION ALL, INTERSECT and EXCEPT added in 8.0.31, operator precedence, and how ORDER BY and LIMIT work with unions.

Joins put tables side by side. Set operations stack query results on top of each other. You use them to merge customers from two systems into one list, to find values that appear in both of two lists, or to find those in one list and not the other. MySQL has had UNION forever. INTERSECT and EXCEPT arrived much later, in MySQL 8.0.31, so you will still see older code that fakes them with joins and subqueries.

This lesson explains the four operators, what makes two queries compatible, how precedence works, and where ORDER BY and LIMIT belong. The examples run live in SQLite, which supports the same operators.

Four ways to combine results

UNION Everything from both, duplicates removed.
UNION ALL Everything from both, duplicates kept.
INTERSECT Only rows found in both.
EXCEPT Rows from the first that are not in the second.

INTERSECT and EXCEPT need MySQL 8.0.31 or newer.

1 / 2

Rules for combining queries

Every query in a set operation must return the same number of columns, and the columns are matched by position, not by name. The names in the result come from the first query. Types should be compatible: MySQL converts them to a common type, and mixing text with numbers produces results that are easy to misread, so keep matching columns the same kind.

Two details matter for correctness:

  • In UNION, INTERSECT and EXCEPT, two NULL values count as equal, even though NULL = NULL is not true in a WHERE clause. Set operations compare whole rows with the "not distinct" rule.
  • Duplicates are removed unless you say ALL.

UNION and UNION ALL

UNION returns every row from either query and removes duplicates, which costs a sort or a hash step. UNION ALL keeps every row and is cheaper. Use UNION ALL whenever you know the inputs cannot overlap, or when duplicates are meaningful (adding up two sets of sales, for instance).

SQL UNION versus UNION ALL

Press Run (or Ctrl + Enter) to see the result here.

The first result has four rows (Ada, Ben, Cara, Dev). The second has six, because Ben and Cara appear in both tables and are kept twice. Typical real uses are combining partitioned or archived tables (orders_2023, orders_2024), merging the results of differently filtered queries into one report, and adding a label column to see where each row came from:

SQL
SELECT name, 'online' AS channel FROM online_customers
UNION ALL
SELECT name, 'store'  FROM store_customers;

INTERSECT and EXCEPT

INTERSECT returns the rows that appear in both results. EXCEPT returns the rows of the first result that do not appear in the second. Both remove duplicates by default, and both accept ALL in MySQL 8.0.31 and later to keep duplicate counts (INTERSECT ALL keeps the smaller count of each row, EXCEPT ALL subtracts counts).

SQL INTERSECT and EXCEPT

Press Run (or Ctrl + Enter) to see the result here.

The intersection is Ben and Cara. Online-only is Ada, and store-only is Dev. Swapping the two queries around EXCEPT changes the answer, unlike UNION and INTERSECT, which are symmetric.

Before 8.0.31

On older servers (5.7, and 8.0 before 8.0.31) these operators raise a syntax error. The usual workarounds are:

SQL
-- INTERSECT, emulated with EXISTS (or an inner join on all columns)
SELECT DISTINCT o.name, o.city
FROM online_customers AS o
WHERE EXISTS (SELECT 1 FROM store_customers s
              WHERE s.name <=> o.name AND s.city <=> o.city);

-- EXCEPT, emulated with NOT EXISTS (or LEFT JOIN ... IS NULL)
SELECT DISTINCT o.name, o.city
FROM online_customers AS o
WHERE NOT EXISTS (SELECT 1 FROM store_customers s
                  WHERE s.name <=> o.name AND s.city <=> o.city);

The null-safe equality operator <=> makes the comparison treat two NULLs as equal, matching the set-operator rule (NULL Handling and Comparison Rules). If you only need a single column, IN and NOT IN also work, with the NOT IN and NULL caveat from the previous lesson. On 8.0.31 or later, use the real operators: they are shorter and say exactly what you mean.

Precedence and parentheses

When you chain several operators without parentheses, MySQL follows the standard: INTERSECT binds tighter than UNION and EXCEPT, and UNION and EXCEPT are evaluated left to right with equal rank. That means

SQL
SELECT 1 UNION SELECT 2 INTERSECT SELECT 2;

is read as SELECT 1 UNION (SELECT 2 INTERSECT SELECT 2) and returns the rows 1 and 2. Do not rely on memory: add parentheses to make the intention obvious. MySQL allows parenthesized query blocks anywhere in a set operation:

SQL
(SELECT name FROM online_customers
 UNION
 SELECT name FROM store_customers)
EXCEPT
SELECT name FROM blocked_customers;

Before 8.0.31, UNION chains were simply evaluated left to right, and mixing UNION and UNION ALL followed that order: a UNION ALL followed by a UNION removed duplicates from everything before it. Parentheses make that explicit too.

SQLite does not accept parentheses around the parts of a compound select, so run the nested example above only on MySQL. You can still see the precedence rule in SQLite, because it evaluates compound selects strictly left to right: the same two lines return a different result there, which is a reminder that precedence differs between databases.

SQL Chained operators are read left to right in SQLite

Press Run (or Ctrl + Enter) to see the result here.

When a query must work on both engines, write each step as a separate subquery in FROM (SELECT * FROM (...) AS t) rather than relying on precedence.

ORDER BY and LIMIT with unions

An ORDER BY or LIMIT written after the last query applies to the whole combined result. Order by a column name or alias from the first query, or by position (ORDER BY 1), because the combined rows no longer belong to any one table.

To sort or limit one part before combining, wrap that part in parentheses (MySQL) or in a derived table (portable):

SQL
(SELECT name, price FROM books WHERE genre = 'Mystery' ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT name, price FROM books WHERE genre = 'Poetry'  ORDER BY price DESC LIMIT 2)
ORDER BY price DESC;

This is the standard way to pick the top rows from each of several queries. Without the parentheses, an ORDER BY before UNION is a syntax error, and a bare LIMIT would apply to the whole union.

SQL Top rows from each part, then one overall sort ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The result is three rows: the most expensive mystery and the two most expensive fiction titles, sorted together by price.

Choosing between set operations and joins

  • To add rows from similar tables, use UNION ALL. A join would add columns instead.
  • To find shared or missing rows over several columns, INTERSECT and EXCEPT are shorter than the EXISTS forms, and they treat NULL the way you usually want.
  • If you need other columns from the matching rows, a join or EXISTS is better, because set operations return only the columns you listed.
  • Large UNION queries (without ALL) build a temporary table to remove duplicates. If memory or disk use looks high in EXPLAIN, check whether ALL is acceptable.
  • Using UNION queries as a way to avoid an OR across different indexed columns is a known optimization technique. Each branch can use its own index.

What ran live here

UNION, UNION ALL, INTERSECT, EXCEPT and derived-table forms of limited parts ran in SQLite. Parenthesized set operations, the ALL variants and the MySQL precedence rule are MySQL 8.0.31 behavior and are shown as read-only code and described results.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

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

    UNION removes duplicate rows, which costs extra work. UNION ALL keeps every row and is cheaper.
  2. 2. In which MySQL version were INTERSECT and EXCEPT added?

    INTERSECT and EXCEPT appeared in 8.0.31. Older versions need EXISTS or join-based workarounds.
  3. 3. How are the columns of the queries in a UNION matched?

    Columns are matched by position, and the result takes its names from the first query.
  4. 4. Where does an ORDER BY written after the last query of a UNION apply?

    A trailing ORDER BY or LIMIT applies to the whole combined result. Use parentheses to sort or limit single parts.
  5. 5. Which statement about EXCEPT is true?

    EXCEPT is not symmetric. It removes duplicates by default and treats two NULLs as equal.

Summary

  • Set operations stack results: the queries need the same number of columns, matched by position, with names from the first query.
  • UNION removes duplicates, UNION ALL keeps them and is faster. In set operations two NULLs count as equal.
  • INTERSECT and EXCEPT exist since MySQL 8.0.31. EXCEPT is not symmetric. Older servers use EXISTS or NOT EXISTS.
  • INTERSECT binds tighter than UNION and EXCEPT. Use parentheses to be explicit.
  • A trailing ORDER BY or LIMIT applies to the whole result. Wrap parts in parentheses to limit them separately.

Next: Common Table Expressions and Recursive CTEs.