Set Operation Requirements and Rules
You'll learn: The three fundamental rules that every set operation (UNION, INTERSECT, EXCEPT) must follow to execute successfully.
The Three Non-Negotiable Rules
When stacking result sets using set operations, SQL enforces strict compatibility requirements—think of it like stacking LEGO blocks: they only connect if the pieces match perfectly.
1. Column Count Must Match
Every query in your set operation must return the exact same number of columns. If the first query selects 3 columns, the second must also select exactly 3 columns.
-- ✓ Valid: Both return 3 columns
SELECT employee_id, first_name, salary FROM employees
UNION
SELECT contractor_id, name, rate FROM contractors;
-- ✗ Invalid: Column count mismatch (3 vs 2)
SELECT employee_id, first_name, salary FROM employees
UNION
SELECT contractor_id, name FROM contractors;
2. Data Types Must Be Compatible
Corresponding columns (by position) must have compatible data types. The first column in query A must match the first column in query B, and so on. SQL typically allows implicit conversions (e.g., INT to DECIMAL), but TEXT to DATE won't work.
-- ✓ Valid: Names are text, IDs are numeric
SELECT name, employee_id FROM employees
UNION
SELECT company_name, account_number FROM clients;
3. ORDER BY Goes at the Very End
You can only use ORDER BY once, after the final query in your set operation. It sorts the combined result set, not individual queries.
-- ✓ Valid: Single ORDER BY at end
SELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name;
Key Takeaway: Set operations require matching column counts, compatible data types in corresponding positions, and a single ORDER BY clause applied to the final combined result—not individual queries.