SQL UNION
UNION stacks the results of two queries on top of each other into a single result set.
Basic UNION
SQL
SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city;
- Both queries must return the same number of columns.
- Column types must be compatible.
- Final column names come from the first query.
UNION vs UNION ALL
| Form | Behaviour |
|---|---|
UNION | Removes duplicates — has to sort/hash. |
UNION ALL | Keeps duplicates — fastest, no extra work. |
Default to UNION ALL unless you specifically want dedup. It's a measurable performance win on large sets.
ORDER BY and LIMIT scope
ORDER BY at the end orders the whole combined result. To order each side independently, wrap them in subqueries:
SQL
(SELECT name FROM customers ORDER BY name LIMIT 5) UNION ALL (SELECT name FROM suppliers ORDER BY name LIMIT 5);
Tip: If you're
UNION-ing similar shaped data from many tables (sharded by month, region…), check whether the data really belongs in one table with an extra column instead.Example
Example
SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city;Try it Yourself »
Exercise
Keep duplicates and run faster — use…
SELECT a FROM t1 UNION
SELECT a FROM t2;
Three letters; modifier that skips dedup.
Discussion
Loading…