Set Compatibility

Definition

Set compatibility (also called union-compatibility) is the structural requirement that two queries combined by UNION/INTERSECT/EXCEPT must share: the same NUMBER of columns, with each corresponding column pair being a compatible TYPE.

How It Works

sql
-- Compatible: both queries return (integer, text)
SELECT id, name FROM students
UNION
SELECT id, title FROM books;   -- works, though "name" and "title" are conceptually different

-- Incompatible: different column counts
SELECT id, name FROM students
UNION
SELECT id, name, email FROM students;   -- ERROR: column count mismatch

Column NAMES don't need to match — only count and type-compatibility matter structurally; the FIRST query's column names/aliases determine the final result's output names (already noted for UNION, 18.1, and equally true for INTERSECT/EXCEPT).

Edge Cases and Pitfalls

• "Compatible types" doesn't always mean "identical types" — many engines allow combining, say, an INT and a DECIMAL column (implicitly converting one), but combining genuinely incompatible types (like a number and a non-numeric string) either errors or produces confusing implicit-conversion results depending on the dialect.
• Set compatibility is checked BEFORE any row-level comparison (deduplication, intersection matching, etc.) happens — a structural mismatch is caught immediately, regardless of what the actual data looks like.
• Being STRUCTURALLY compatible (right column count/types) doesn't mean the combination is SEMANTICALLY meaningful — SELECT id, name FROM students UNION SELECT id, name FROM books runs without error even though mixing student names and book titles under one column is probably nonsensical for the actual use case; the database only checks structure, not intent.

Key Takeaways

• Set compatibility requires matching column COUNT and compatible TYPES between combined queries — not matching column names.
• The first query's column names/aliases name the final combined result.
• Structural compatibility is checked before any data comparison — but passing that check doesn't guarantee the combination is semantically sensible.