SQL UNION vs UNION ALL: Combining Query Results

The rules

Each part of a union must return the same number of columns, matched by position, not by name. The result takes its column names from the first SELECT, and a single ORDER BY after the last part sorts the combined rows.

Use UNION ALL whenever duplicates are meaningful, such as transactions in a ledger. Use UNION for "distinct values from either list".

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT customer_id FROM orders WHERE order_date < '2025-02-01'
UNION
SELECT customer_id FROM orders WHERE order_date >= '2025-03-01'
ORDER BY customer_id;

SELECT customer_id FROM orders WHERE order_date < '2025-02-01'
UNION ALL
SELECT customer_id FROM orders WHERE order_date >= '2025-03-01'
ORDER BY customer_id;

WITH ledger AS (
  SELECT order_id, amount FROM orders
  UNION ALL
  SELECT order_id, -amount FROM refunds
)
SELECT o.customer_id, SUM(l.amount) AS net_revenue
FROM ledger l
JOIN orders o ON o.order_id = l.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

Common mistakes

• UNION in a ledger. Two refunds of the same amount on the same order would collapse into one with UNION. Use UNION ALL for transactions.
• Mismatched columns. Different column counts are an error; columns in a different order are not, and quietly mix up data. Line columns up by position.
• ORDER BY in each part. Only the final ORDER BY sorts the result. Ordering inside a part needs parentheses and usually has no effect.

Practise UNION vs UNION ALL

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Camera Buyers Without Memory Cards

   Find customers who bought a camera but never bought a memory card, and count their cameras.

   Medium✓ Solved
2. 2

   Most Connected Members

   Find the member or members with the most friends, counting both sides of each friendship.

   Medium✓ Solved
3. 3

   Income Band Counts

   Count accounts in three fixed income bands, showing bands with no accounts as zero.

   Medium✓ Solved
4. 4

   Top Rater and Top Movie

   Return the most active reviewer and the best-rated movie of February in a single result.

   Medium✓ Solved
5. 5

   People You May Know

   Recommend non-friends who share at least two mutual friends.

   Hard✓ Solved
6. 6

   Monthly Net Revenue by Customer

   Net payments, refunds and chargebacks into the month each money movement happened.

   Hard✓ Solved

Want a full set? Work through the Subqueries & CTEs study plan.

Questions people ask

Is UNION slower than UNION ALL?

Usually, yes: removing duplicates needs an extra sort or hash step. Prefer UNION ALL when duplicates are impossible or wanted.

What about INTERSECT and EXCEPT?

PostgreSQL supports both, and MySQL supports them since 8.0.31. They keep rows found in both queries, or in the first but not the second.

Related concepts

• SQL CTE (WITH Clause) Explained with Examples
• SQL GROUP BY Explained with Examples