Set operations and duplicate meaning

The idea

Compound queries combine compatible result sets. UNION removes duplicate complete rows, while UNION ALL retains every occurrence. INTERSECT returns shared rows, and EXCEPT returns rows from the left result that do not appear in the right. The meaning of duplicates determines the operator: repeated events may represent real observations, while a directory of distinct identifiers may require membership only.

Both sides must produce the same number of columns, and corresponding columns should represent comparable concepts. Duplicate elimination applies to complete selected rows, so adding another column can change which rows count as duplicates. A compound query still needs an explicit final ORDER BY when its presentation order matters. Do not rely on branch order or an observed execution plan to establish a guaranteed output sequence.

WITH a(x) AS (VALUES(1),(2)), b(x) AS (VALUES(2),(3))
SELECT x FROM a UNION ALL SELECT x FROM b ORDER BY x;

x
1
2
2
3

UNION ALL preserves both observations of two; the final ordering controls their presentation.