Set Operations: UNION, INTERSECT, EXCEPT

## The two operators A set operator takes two complete query results and produces one. `UNION ALL` is pure concatenation: every row from the left query, then every row from the right query, with nothing removed. `UNION` does the same concatenation and then removes duplicate rows from the combined result. In standard SQL the keyword after the operator is optional and spells out the choice — `UNION ALL` versus `UNION DISTINCT` — and bare `UNION` means `UNION DISTINCT`. The same `ALL`/`DISTINCT` choice exists in the standard for `INTERSECT` and `EXCEPT`, where `DISTINCT` is likewise the default. ```sql SELECT city FROM customers UNION ALL SELECT city FROM suppliers; ``` ## Duplicate elimination is whole-row This is the point that trips people up in practice. `UNION` does not deduplicate on some key column; it compares the entire output row. Two rows are duplicates only if every corresponding column holds the same value. So this returns one row per distinct city: ```sql SELECT city FROM customers UNION SELECT city FROM suppliers; ``` but adding a second column that differs between the branches makes almost nothing a duplicate any more: ```sql SELECT city, 'customer' AS source FROM customers UNION SELECT city, 'supplier' AS source FROM suppliers; ``` Here the literal `source` column differs on every pair, so `UNION` removes nothing across the branches — it can still remove duplicates within one branch. That is the second surprise: `UNION` deduplicates the *combined* result, so if `customers` contains the same city three times, plain `UNION` collapses those three to one even though the second query was never involved. If you wanted "all customer cities plus supplier cities not already listed", `UNION` gives you something subtly different from that. ## Cost Removing duplicates means the engine must compare rows against each other, which is real work proportional to the size of the combined result — and work that `UNION ALL` simply never does. In a query that produces a large intermediate result, the difference between the two operators can dominate the runtime, and it is one of the cheapest wins available: replacing `UNION` with `UNION ALL` where the branches are provably disjoint costs nothing and removes an entire processing step. ## Choosing between them The decision is a correctness question first and a cost question second: - If duplicates are **impossible by construction** — the branches read disjoint sets of rows, or carry a discriminating column like `source` above — use `UNION ALL`. It is equivalent and cheaper. - If duplicates are **meaningful** — you are stacking two event streams and each row is a real occurrence — use `UNION ALL`, because `UNION` would silently destroy data. - If you genuinely need a set of distinct rows and cannot argue the branches apart, use `UNION` and let the engine do it. A useful habit: write `UNION ALL` by default, and when you switch to `UNION`, be able to say out loud which duplicates you are removing. "I used `UNION` to be safe" is the answer that hides bugs, because the operator is not being safe — it is masking whatever duplicate-producing defect exists upstream, such as an unintended join fan-out. ## Things that are the same for both Both operators require the two queries to be compatible: the same number of columns, matched **by position** rather than by name, with types that can be resolved to a common type. Both produce a result whose column names come from the first branch, which is why an `ORDER BY` written after the set operation has to use those names or ordinal positions. Neither operator guarantees any particular row order — not even "left branch first" for `UNION ALL`. If order matters, add a single `ORDER BY` at the end of the whole statement. ## Portability `UNION` and `UNION ALL` are the most universally implemented parts of SQL's set-operator family; you can rely on both in any SQL engine you are likely to meet. The `DISTINCT` spelling and the `ALL` forms of the other set operators are less uniformly available, so when you need those, check your engine's documentation.