What is the difference between UNION and UNION ALL?

UNION combines the rows of two queries and removes duplicate rows, which requires an extra sort or hash pass. UNION ALL combines the rows and keeps every duplicate, so it is faster. Use UNION ALL unless you specifically need duplicates removed.

Do both queries in a UNION need the same columns?

Yes. Both SELECT statements must return the same number of columns, in the same order, with compatible data types. UNION matches columns by position rather than by name, and the result uses the column names from the first query.

What is the difference between UNION and JOIN?

A JOIN combines columns from related tables side by side, making each row wider. A UNION stacks the rows of two queries on top of each other, making the result longer. Use JOIN to match related records and UNION to append one result set to another.

Beginner

UNION vs UNION ALL in SQL: Combining Query Results

Stack the rows of two queries into one result. Learn the rules UNION enforces, why UNION ALL is faster, and how UNION differs from a JOIN — with clear PostgreSQL examples.

UNION stacks the rows of two queries on top of each other into a single result set. Where a JOIN combines columns side by side, UNION combines rows end to end. It is how you merge, say, archived and active orders, or two sources that share the same shape. Keep the SQL cheat sheet open while you read.

What UNION Does

Write two SELECT statements and join them with UNION. The result contains every row from the first query followed by every row from the second.

-- All contact emails from two tables, in one list
SELECT email FROM users
UNION
SELECT email FROM newsletter_subscribers;

The Rules UNION Enforces

Both queries must produce the same number of columns, in the same order, with compatible types. The column names come from the first query; the second query’s names are ignored.

-- Column count and types must line up positionally
SELECT id, name, 'customer' AS source FROM users
UNION
SELECT id, company_name, 'vendor'  AS source FROM vendors;

UNION ALL & Duplicates

Plain UNION removes duplicate rows, which forces the database to sort or hash the entire combined result — work that is wasted if you know there are no duplicates. UNION ALL keeps every row and is therefore faster. Reach for UNION ALL by default and only use UNION when you genuinely need de-duplication.

-- Keeps duplicates, no de-dup pass — faster
SELECT product_id FROM orders_2025
UNION ALL
SELECT product_id FROM orders_2026;

-- Removes duplicates — only when you need to
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

UNION stacks rows — Two result sets are stacked end-to-end into one. UNION ALL keeps every row; plain UNION removes duplicate rows like the repeated value here.

Sorting a Combined Result

A single ORDER BY at the very end sorts the whole combined result. You cannot sort each half separately with its own ORDER BY (except inside a subquery with LIMIT).

SELECT name, created_at FROM users
UNION ALL
SELECT name, created_at FROM archived_users
ORDER BY created_at DESC;

UNION vs JOIN

They solve different problems. JOIN adds columns from a related table to each row — widening the result. UNION adds rows from another query — lengthening the result. If you are matching records by a key, you want a JOIN; if you are appending one list to another, you want UNION.

Common Mistakes & Tips

Using UNION when UNION ALL would do — the de-duplication pass is pure overhead if there are no duplicates
Mismatched column counts or types between the two queries — a hard error
Relying on column order silently lining up — UNION matches by position, not name
Adding ORDER BY to the first query expecting it to sort only that half
Reaching for UNION when you actually need a JOIN (or vice versa)

UNION and UNION ALL come up constantly in reporting. Practice combining result sets on real tables to make the column-and-type rules automatic.