UNION ALL

Definition

UNION ALL combines the rows of two queries WITHOUT removing duplicates — every row from both sides appears in the result, including exact repeats.

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 ALL
SELECT student_id FROM enrolled_2026
ORDER BY student_id;

This returns 1, 2, 2, 3, 3, 4, 5 — students 2 and 3 (enrolled both years) each appear TWICE, once from each source query. This is the direct trade-off against plain UNION (18.1): faster (no de-duplication work) but includes repeats.

Edge Cases and Pitfalls

• UNION ALL is the right choice whenever duplicates are IMPOSSIBLE by construction (e.g. combining data you already know comes from non-overlapping sources) or when you specifically WANT to preserve and count repeats — using plain UNION in that situation wastes effort de-duplicating something that either can't have duplicates or shouldn't lose them.
• A common mistake is defaulting to UNION out of habit even when duplicates genuinely can't occur — this silently costs performance for a de-duplication guarantee nobody needed.
• UNION ALL has no equivalent as a basic operation in the classic set-based relational algebra (7.4's Union assumes set/duplicate-free semantics) — it's a SQL-specific, "bag semantics" extension, reflecting that real SQL tables behave like multisets, not pure sets.

Key Takeaways

• UNION ALL combines rows from two queries with no de-duplication — faster, but keeps repeats.
• Use it whenever duplicates are impossible or don't matter — it avoids UNION's unnecessary de-duplication cost.
• It has no classic relational-algebra equivalent — a SQL-specific extension reflecting SQL's bag (not pure set) semantics.