UNION, INTERSECT, EXCEPT

By the end of this lesson you can stack the results of whole queries and compare two result sets row by row, including rows that contain NULL.

In Subqueries you saw that NOT IN returns nothing when the set holds a NULL, and that NOT EXISTS is the correlated fix. A set operator answers the same "in A but not in B" question a third way, and it compares whole rows.

Four operators, one table pair

A set operator joins two SELECTs by stacking their rows, where a join glues columns side by side. The examples use two tables of city offices:

east                    west
city  zone              city  zone
Oslo  N                 Oslo  N
Oslo  N                 Rome  E
Rome  S                 Kyiv  NULL
Kyiv  NULL              Lima  W

Each result is SELECT city, zone FROM east, the operator, then SELECT city, zone FROM west. UNION, INTERSECT and EXCEPT remove duplicate rows from their result (Oslo/N is listed twice in east and appears once). UNION ALL is the only one that keeps duplicates. SQLite has no INTERSECT ALL or EXCEPT ALL; writing them is a syntax error. SELECT DISTINCT is that same de-duplication on a single query.

EXCEPT is not symmetric: the last two table rows differ.

Rows are compared as a whole, and NULL equals NULL

Two rows are "the same" when every column matches, and for this comparison two NULLs count as equal. That is different from =, where NULL = NULL is unknown. In the tables above Kyiv/NULL appears on both sides, so INTERSECT returns it and EXCEPT removes it.

Which zones does east have that west lacks, given a NULL in west.zone?

SELECT zone FROM east WHERE zone NOT IN (SELECT zone FROM west);   -- no rows
SELECT zone FROM east EXCEPT SELECT zone FROM west;                -- S

The NULL in the subquery empties the first; the second prints S. A join with ON w.zone = e.zone fails the other way: it never matches Kyiv/NULL to Kyiv/NULL, so an unchanged row looks different.

The rules SQLite enforces

• Same number of columns on both sides, or you get SELECTs to the left and right of UNION do not have the same number of result columns. Column names come from the first SELECT.
• One ORDER BY / LIMIT, at the very end, applying to the whole result. An ORDER BY before the operator is an error. Its terms must be result columns or positions: ORDER BY zone after SELECT city ... fails with 1st ORDER BY term does not match any column in the result set.
• Strictly left to right. SELECT 1 UNION SELECT 2 INTERSECT SELECT 2 computes (1 UNION 2) INTERSECT 2 and prints only 2. A database that gives INTERSECT higher precedence would print 1 and 2.
• No parentheses around a member. (SELECT 1) UNION (SELECT 2) is a syntax error. To control grouping, put a member in a subquery in FROM:

SELECT city FROM east
UNION
SELECT city FROM (SELECT city FROM west EXCEPT SELECT city FROM east)
ORDER BY city;

That prints Kyiv, Lima, Oslo, Rome.

A literal in the SELECT list tags where rows came from, which is how you tell stacked rows apart: SELECT 'east' AS side, city FROM east UNION ALL SELECT 'west', city FROM west returns eight rows, each labelled. Name the column in the first SELECT, so a final ORDER BY side works.

Common mistakes

• Using UNION when you need every row: the two tables give 8 rows with UNION ALL and 5 with UNION.
• Writing EXCEPT the wrong way round: west-first gives Lima and Rome, not Rome.
• Comparing on one column when the question is about whole rows. Rome is in both tables, but Rome/S and Rome/E are different rows, so a city-only comparison misses the change.
• Wrapping a member in parentheses, or putting ORDER BY on it: both are errors.

Your exercise

You get two snapshots of a membership roster and must print one change report: whole rows found in only one snapshot, tagged added or removed. The most likely mistake is comparing one column or using =, which misreports members who appear twice, have no email, or changed email.