UNION

Definition

This topic revisits `UNION` — already introduced conceptually in relational algebra (7.4) — as a concrete SQL set operation: combining the rows of two queries into one result, keeping every DISTINCT row from either.

Running example — enrolled_2025(student_id) and enrolled_2026(student_id), tracking which students were enrolled each year:

enrolled_2025: 1, 2, 3, 4 enrolled_2026: 2, 3, 5

(Students 2 and 3 stayed enrolled both years; 1 and 4 left after 2025; 5 is a new student in 2026.)

sql
SELECT student_id FROM enrolled_2025
UNION
SELECT student_id FROM enrolled_2026
ORDER BY student_id;

This returns every student enrolled in EITHER year — 1, 2, 3, 4, 5 — with automatic de-duplication (students 2 and 3, present in both tables, appear only once each).

Edge Cases and Pitfalls

• ORDER BY can only appear ONCE, at the very end of the whole UNIONed statement — it sorts the FINAL combined result, not either query individually; you cannot ORDER BY inside one of the two SELECTs that make up the union (with rare dialect-specific exceptions for a subquery wrapped in parentheses).
• Both queries combined by UNION must return the same number of columns with compatible types (union-compatibility, 18.5) — this is checked before any row-level de-duplication happens.
• UNION's automatic de-duplication (18.6) has a real computational cost — if you know duplicates can't occur, or don't care about them, UNION ALL (18.2) skips that cost entirely.

Key Takeaways

• UNION combines two queries' rows, keeping the DISTINCT set from either.
• A single ORDER BY at the very end sorts the combined result — it cannot apply to just one side.
• De-duplication has a real cost; UNION ALL (18.2) skips it when duplicates aren't a concern.