Lesson 33 of 61

5 mins read

SQL UNION: Combining Result Sets with Automatic Deduplication

In Plain English

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.

The Mental Model

"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

Vertical Stacking: Combines row outputs rather than expanding columns.

Automatic Deduplication: Filters out duplicate records across both query results.

First SELECT Headers: Column aliases declared in the first SELECT define final output column names.

Common Beginner Traps & How to Fix Them

Mistake: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.

Pro Tip: If you do NOT need deduplication, always prefer `UNION ALL`: it is dramatically faster because it skips the expensive sorting and hashing pass.

Live Interactive Example

Hit Run Code to see it live

Combining Customer and Vendor Contacts with UNION

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: UNION combined emails from both tables and automatically eliminated the duplicate [email protected].

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Combine Distinct Values with UNION

Combine `SELECT city FROM offices UNION SELECT city FROM warehouses ORDER BY city;`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.