Union

Definition

Union (symbol: ∪) combines the rows of two relations into one, keeping every distinct row that appears in EITHER (or both). For Union to be valid, the two relations must be union-compatible: the same number of columns, with matching (or compatible) data types in the same positions.

How It Works

sql
SELECT student_id FROM honors_students
UNION
SELECT student_id FROM deans_list
ORDER BY student_id;

This returns every student_id that appears on the honors list, the dean's list, or both — with duplicates automatically removed, matching the algebra's set semantics (a relation is a set; ∪ on two sets naturally has no duplicates). SQL's plain UNION removes duplicates for exactly this reason; UNION ALL is the SQL-specific variant that keeps duplicates (useful when you specifically want to preserve them, e.g. for accurate counting), but UNION ALL has no equivalent as a basic operation in the classic set-based relational algebra.

Edge Cases and Pitfalls

• The two queries combined by UNION must return the SAME NUMBER of columns, and corresponding columns should be comparable types — mismatched column counts are a straightforward SQL error, but mismatched-but-technically-compatible types (e.g. combining an INT column with a VARCHAR column in the same position) can silently produce confusing implicit conversions instead of an error.
• Output column NAMES in a UNION come from the FIRST query's column list; the second query's column aliases are ignored for naming purposes (though its actual values are still included).
• UNION performs duplicate elimination by comparing entire rows — two rows are only merged into one if every corresponding column matches, not just some of them.

Key Takeaways

• Union (∪) combines rows from two union-compatible relations, keeping all distinct rows.
• SQL's UNION matches the algebra's duplicate-free behavior; UNION ALL is a SQL-only variant that keeps duplicates.
• Both relations combined by Union must have the same number of (type-compatible) columns.