UNION, INTERSECT and EXCEPT
Two separate SELECTs, stacked or compared row by row. Pick an operator and watch which names survive.
Operator
A = 2024 conference attendees, B = 2025 attendees
Legend
A only
B only
in both A and B
Every Name From Both Queries
—Query
Result
Rows Returned
Overview
UNION returns rows that appear in either query and removes duplicates. UNION ALL keeps every row, including duplicates. INTERSECT keeps rows common to both results. EXCEPT keeps rows from the first result that do not appear in the second.
Three ways to combine result sets
This produces the combined set of email values. Use UNION ALL when duplicates are meaningful or have already been handled; it avoids the duplicate-elimination work required by UNION.
The result columns must line up
Each query must return the same number of columns in the same order, with compatible types. Column names generally come from the first SELECT. Set operators combine values by position; they do not match columns by name.
If order matters, apply ORDER BY to the final combined result. Parentheses and precedence rules differ in how complex combinations are parsed, so use parentheses to make intent clear. Support for INTERSECT and EXCEPT varies across older engines.
Choose between a set operation and a join
Use a set operator when each query produces a compatible list of values to stack or compare. Use a join when columns from related rows should appear side by side. See joins. If the input query includes NULL, duplicate handling follows the set operation's rules, which may differ from ordinary equality predicates.
Duplicate removal changes meaning
UNION treats duplicate result rows as one row, comparing the complete output row rather than a chosen key. If two source tables contain the same email but different names, both rows remain because the rows are not identical. UNION ALL preserves both regardless. If the desired rule is “one row per email, choosing the newest record,” a set operator alone does not express which record wins; use ranking or an aggregate with an explicit selection rule.
Align types and ordering deliberately
Corresponding columns are paired by position, so a name in column one of the first query is combined with column one of the next query even when that column has a different alias. Give each SELECT the same intended meaning and compatible types at every position. If the output needs a label or stable order, name columns in the first query and sort the final compound result. An ORDER BY inside an individual branch does not define the final combined order and may be disallowed without a limiting clause.
Further reading
- SQLite SELECT documents compound SELECT and set operators.
- PostgreSQL combining queries explains UNION, INTERSECT, and EXCEPT.