UNION & UNION ALL

The UNION and UNION ALL operators combine the result sets of two or more SELECT queries into a single result set, stacking the rows of each query on top of one another. This is different from a JOIN, which combines columns from different tables side by side — a UNION combines rows.

Rules for combining SELECTs

Every SELECT in a UNION must return the same number of columns, and the columns must line up by position with compatible data types. Column names in the final result come from the first SELECT.

Basic syntax

SQL

SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;

UNION removes duplicates

Plain UNION combines the rows from both queries and then removes any duplicate rows from the final result, as if DISTINCT had been applied across the combined set.

UNION has a performance cost

To remove duplicates, the database has to compare every row against every other row, which typically means sorting or hashing the entire combined result set. On large result sets this extra pass can be noticeably slower than UNION ALL. Only pay that cost when you actually need deduplicated output.

UNION ALL keeps everything

UNION ALL simply concatenates the rows from each SELECT without checking for duplicates. If the same row appears in both queries, it will appear twice in the output. Because there is no deduplication step, UNION ALL is almost always faster than UNION.

Tip

Use UNION ALL by default. Only reach for plain UNION when duplicate rows would genuinely be wrong for your use case — for example, when merging two lists of email addresses that are expected to overlap and you want each address once.

Worked example

Suppose you track customers who placed orders this month in one table, and customers who signed up for a newsletter in another. You want a single combined mailing list.

orders and newsletter_signups

SQL

-- orders table
-- customer_email
-- ------------------
-- alice@example.com
-- bob@example.com
-- alice@example.com   (bought twice)

-- newsletter_signups table
-- customer_email
-- ------------------
-- bob@example.com
-- carol@example.com

SELECT customer_email FROM orders
UNION
SELECT customer_email FROM newsletter_signups;

-- Result (deduplicated):
-- alice@example.com
-- bob@example.com
-- carol@example.com

SELECT customer_email FROM orders
UNION ALL
SELECT customer_email FROM newsletter_signups;

-- Result (all rows, including the repeat):
-- alice@example.com
-- bob@example.com
-- alice@example.com
-- bob@example.com
-- carol@example.com

UNION vs UNION ALL

Aspect	UNION	UNION ALL
Duplicates	Removed	Kept
Performance	Slower (extra dedup pass)	Faster (no dedup)
Typical use	You need a distinct combined set	You just need to stack results, or duplicates cannot occur

Ordering combined results

An ORDER BY clause can only appear once, at the very end, and applies to the combined result of all the SELECT statements — not to each one individually.

SQL

SELECT customer_email, 'order' AS source FROM orders
UNION ALL
SELECT customer_email, 'newsletter' AS source FROM newsletter_signups
ORDER BY customer_email;

UNION and UNION ALL stack rows from multiple SELECT queries.
Column count and compatible types must match across all SELECTs.
UNION deduplicates; UNION ALL does not.
Prefer UNION ALL for performance unless duplicates are a problem.
Only one ORDER BY, placed after the last SELECT, applies to the whole result.