Set Operations

UNION, UNION ALL, INTERSECT and EXCEPT stack or compare the rows of two queries. Know how each treats duplicates and NULLs, and how they bind when mixed.

Overview

A set operation takes the rows of two SELECT queries and combines them into one result. The two queries must be union compatible: the same number of columns, with compatible types in each position. The result's column names come from the first query.

There are four of them. Three remove duplicate rows by default, and one keeps them.

  • UNION — rows in either query — removed
  • UNION ALL — every row of both queries, stacked — kept
  • INTERSECT — rows in both queries — removed (INTERSECT ALL keeps some)
  • EXCEPT — rows in the first query and not in the second — removed (EXCEPT ALL keeps some)

Oracle spells EXCEPT as MINUS; its 21c manual also accepts EXCEPT as a synonym. MySQL added INTERSECT and EXCEPT only in 8.0.31.

The four operations on one example

Two small tables of sign-up emails. Ben signed up twice on the web and once in a store.

Run the same pair of queries with each operator and you get these rows.

  • UNION — ana, ben, cy, dee
  • UNION ALL — ana, ben, ben, ben, cy, dee (6 rows)
  • INTERSECT — ben
  • EXCEPT — ana, cy
  • EXCEPT ALL — ana, ben, cy

UNION ALL is the cheapest: the database only appends rows. The other three must find duplicates, which means a sort or a hash over the combined rows. So use UNION ALL whenever duplicates are impossible or harmless, and UNION only when you want them removed.

How ALL counts duplicates

With ALL, PostgreSQL keeps duplicates by counting. If a row appears m times on the left and n times on the right, the result has it this many times.

  • UNION ALL — m + n — 3
  • INTERSECT ALL — min(m, n) — 1
  • EXCEPT ALL — max(m − n, 0) — 1
  • ana@x.com
  • ben@x.com
  • cy@x.com

NULLs count as equal

Set operations treat two NULLs as the same row, the way DISTINCT does. A WHERE clause does not: NULL = NULL is unknown, not true. This is why EXCEPT and NOT IN can give different answers.

  • 1

The same question written with NOT IN returns no rows at all. Because right_ids holds a NULL, 1 NOT IN (2, NULL) is unknown rather than true.

  • (no rows)

Precedence: INTERSECT binds first

When you mix operators, the SQL standard, PostgreSQL and MySQL evaluate INTERSECT before UNION and EXCEPT. UNION and EXCEPT then run left to right. Oracle is the exception: it gives all set operators equal precedence and reads left to right, and its docs tell you to use parentheses.

  • 1
  • (no rows)

Always add parentheses when you mix operators. It costs nothing and every reader, and every database, agrees on the meaning.

ORDER BY and LIMIT apply to the whole result

An ORDER BY or LIMIT written after the last query sorts or cuts the combined result, not the last query. To limit one branch, put that branch in parentheses. Refer to columns by the names the first query gave them.

  • ana@x.com
  • ben@x.com

A useful habit is to add a literal column that says where each row came from, such as 'web' AS source. Note that with UNION this also changes which rows count as duplicates.

Set operation or join?

EXCEPT and INTERSECT answer the same questions as some joins and subqueries, but they compare whole rows and ignore everything else. Pick by what the result needs.

  • Rows in A and not in B — EXCEPT (whole rows; NULLs match) — NOT EXISTS or LEFT JOIN ... IS NULL (can return other columns of A)
  • Rows in both A and B — INTERSECT (NULLs match) — INNER JOIN or EXISTS (NULL keys never match)
  • Stack two similar tables — UNION ALL — none

INTERSECT is not the same as an inner join on every column. A join compares with =, so a row with a NULL in any column never matches, while INTERSECT keeps it.

What you will be able to do

  • Pick UNION ALL or UNION and say what the deduplication costs.
  • Predict the result of INTERSECT ALL and EXCEPT ALL from duplicate counts.
  • Explain why EXCEPT and NOT IN disagree when the data has NULLs.
  • Write mixed set operations with parentheses and put ORDER BY and LIMIT where they belong.

When to use it

  • Stacking rows from tables with the same shape, such as current and archived orders
  • Finding rows that appear in both of two queries (INTERSECT)
  • Finding rows in one query that are missing from another (EXCEPT)
  • Comparing two versions of a table to find added or removed rows
  • Turning columns into rows by stacking one SELECT per column
  • Building a small list of literal values with SELECT ... UNION ALL SELECT ...

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio