Python SQL UNION: Combining Result Sets with Automatic Deduplication
The `UNION` operator combines the result sets of two or more `SELECT` statements into a single combined result set, automatically removing duplicate rows. Unlike joins (which combine columns horizontally), `UNION` stacks rows vertically.
"A JOIN is taping two cards together side by side (wider); a UNION is stacking two decks of cards on top of each other into a single tall deck (taller)."
Deep Dive: How It Works
Structural Rules: 1) Each SELECT must have the EXACT SAME number of columns. 2) Corresponding columns must have compatible data types. 3) Output column names are taken from the FIRST SELECT statement.
Automatic Deduplication: `UNION` performs a sort and hash deduplication pass to eliminate duplicate rows across both queries.
Final ORDER BY: A single `ORDER BY` clause placed at the very end sorts the entire combined union result.
Syntax Blueprint
SELECT email, 'Customer' AS source FROM customers UNION SELECT email, 'Supplier' AS source FROM suppliers ORDER BY email ASC;
UNION vertically stacks rows from multiple queries and removes duplicates.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Putting an `ORDER BY` clause inside individual SELECT queries before the UNION keyword.Why it happens: Syntax error in standard SQL: individual union branches cannot have isolated ORDER BY clauses.
How to fix: Place one single `ORDER BY` clause at the very end of the final union statement.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Combine Distinct Values with UNION
- Combine `SELECT city FROM offices UNION SELECT city FROM warehouses ORDER BY city;`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.