UNION vs UNION ALL in SQL Explained Simply

A JOIN puts tables side by side: it adds columns. UNION stacks query results on top of each other: it adds rows. And there are two versions, UNION and UNION ALL, that differ in one important way:

• UNION removes duplicate rows.
• UNION ALL keeps every row, including duplicates.

The example tables

Two branches each keep their own customer list:

CustomersManama
Name	City
Ali	Manama
Sara	Manama
Omar	Riffa

CustomersRiffa
Name	City
Omar	Riffa
Huda	Riffa

Omar buys from both branches, so he appears in both lists.

UNION: one list, no duplicates

SELECT Name, City FROM CustomersManama
UNION
SELECT Name, City FROM CustomersRiffa;

Name	City
Ali	Manama
Huda	Riffa
Omar	Riffa
Sara	Manama

4 rows: Omar appears once. Notice the order may change. To remove duplicates, the database often sorts the rows. Without ORDER BY, never rely on the order of the result.

UNION ALL: everything, as it is

SELECT Name, City FROM CustomersManama
UNION ALL
SELECT Name, City FROM CustomersRiffa;

Name	City
Ali	Manama
Sara	Manama
Omar	Riffa
Omar	Riffa
Huda	Riffa

5 rows: Omar appears twice.

Which one is faster?

UNION ALL is faster, because it just appends the second result to the first. UNION has to compare every row with every other row to find duplicates. In the execution plan that shows up as a Sort (Distinct Sort) or a Hash Match, and on large results it can use a lot of memory and tempdb.

So the rule is:

• Use UNION ALL by default.
• Use UNION only when duplicates are possible and you really want them removed.

If the two queries can never return the same row (for example, one returns 2025 data and the other 2026 data), UNION does extra work for nothing.

A trap: UNION can hide real data

Combining payments from two sources:

SELECT CustomerID, Amount FROM CashPayments
UNION
SELECT CustomerID, Amount FROM CardPayments;

If a customer paid 50 in cash and 50 by card, the two rows are identical, and UNION keeps only one. The total comes out 50 short. When you add up money, counts or anything else, use UNION ALL. Better still, add a column that tells the rows apart:

SELECT CustomerID, Amount, 'Cash' AS Source FROM CashPayments
UNION ALL
SELECT CustomerID, Amount, 'Card' AS Source FROM CardPayments;

The rules every UNION must follow

1. Same number of columns in every query.
2. Compatible data types in the same position. A number in column 1 of the first query needs a number (or something convertible) in column 1 of the second.
3. Column names come from the first query. Put your aliases there.
4. Only one ORDER BY, at the very end. It sorts the combined result.

SELECT Name, City, 'Manama' AS Branch FROM CustomersManama
UNION ALL
SELECT Name, City, 'Riffa' FROM CustomersRiffa
ORDER BY Name;

How UNION treats NULL

In a WHERE clause, NULL = NULL is not true. But when UNION removes duplicates, two rows that both have NULL in the same column are treated as duplicates. ('Ali', NULL) and ('Ali', NULL) become one row. DISTINCT works the same way. See NULL in SQL Explained Simply.

A practical use: replacing a slow OR

Sometimes a query with OR on two different columns can’t use an index and scans the whole table:

SELECT OrderID FROM Orders
WHERE CustomerCode = 'C1001' OR SalesRep = 'R07';

Splitting it lets each half seek on its own index:

SELECT OrderID FROM Orders WHERE CustomerCode = 'C1001'
UNION
SELECT OrderID FROM Orders WHERE SalesRep = 'R07';

Here UNION (not UNION ALL) is correct, because an order matching both conditions should appear once, just like with OR. Always compare the plan and the logical reads before and after. See Index Seek vs Index Scan.

INTERSECT and EXCEPT

Two relatives of UNION:

-- Customers in BOTH lists (Omar)
SELECT Name FROM CustomersManama
INTERSECT
SELECT Name FROM CustomersRiffa;

-- Customers in Manama's list but NOT in Riffa's (Ali, Sara)
SELECT Name FROM CustomersManama
EXCEPT
SELECT Name FROM CustomersRiffa;

SQL Server and PostgreSQL support both. MySQL added them in version 8.0.31. Oracle calls EXCEPT MINUS.

UNION vs UNION ALL at a glance

	UNION	UNION ALL
Duplicates	Removed	Kept
Speed	Slower (sort or hash)	Faster
Row order	Not guaranteed	Not guaranteed
Safe for totals	No, may drop real rows	Yes
Use when	You want a distinct list	Almost every other time

Related articles

• HAVING vs WHERE in SQL: filtering rows and groups.
• SQL Subqueries vs. CTEs Explained Simply: other ways to build a query from smaller queries.
• How to Read a SQL Server Execution Plan: spot the Sort that UNION adds.