SQL UNION and UNION ALL Explained

Joins combine tables side by side, adding columns. UNION and UNION ALL do the opposite — they stack the results of two separate SELECT queries on top of each other, combining rows rather than columns. This is useful whenever you need a single result set pulled from two queries that return the same shape of data, whether from the same table or two different ones.

Both operators require the two queries to return the same number of columns, in the same order, with compatible data types. The column names in the final result come from the first query — the second query's column names are ignored even if they differ.

SQL UNION: Combining Results and Removing Duplicates

Suppose you keep a list of customers (id, name, city) and a separate list of suppliers (id, name, city), and you want one combined list of every city you do business with:

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Result:

city
---------
Chicago
Austin
Denver
Seattle

If both customers and suppliers had a row with city = 'Chicago', UNION would only show it once. To do this, the database has to compare every row against every other row and discard duplicates — which means UNION does real extra work (effectively an implicit DISTINCT pass) on top of running both queries.

SQL UNION ALL: Combining Results and Keeping Everything

UNION ALL skips the deduplication step entirely — it just concatenates both result sets, duplicates and all:

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Result:

city
---------
Chicago
Austin
Denver
Chicago
Seattle

Here "Chicago" appears twice, once from each table. Because there's no deduplication work to do, UNION ALL is noticeably faster than UNION on large result sets — so if you already know your data has no duplicates, or you simply don't care about them, UNION ALL is the better default.

UNION vs UNION ALL: Which Should You Use?

A simple rule of thumb: reach for UNION ALL first, and only switch to plain UNION if you specifically need duplicates removed. Many developers default to UNION out of habit without realizing they're paying a performance cost for a deduplication step they didn't actually need.

Both operators also require matching column counts and types — mismatching them (e.g. combining a query that selects 2 columns with one that selects 3) will raise an error rather than silently padding the result with NULLs.

---