✏️ Explanatory Question
Level: Intermediate — A conceptual favourite; both combine data but in completely different directions.
Both combine data from multiple tables, but the direction of the combination is the key difference:
UNION removes duplicates; UNION ALL keeps them.| Feature | JOIN | UNION |
|---|---|---|
| Direction | Horizontal (columns) | Vertical (rows) |
| Combines | Related columns | Similar result sets |
| Requires | A join condition (ON) | Same column count & types |
| Duplicates | N/A | UNION removes, UNION ALL keeps |
| Result shape | Wider table | Taller table |
-- JOIN: add department columns to each employee (horizontal)
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- UNION: stack two lists of names into one column (vertical)
SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;
-- UNION ALL: keep duplicates (faster, no dedupe)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;