MS SQL Server UNION and UNION ALL

Hello friends, welcome to shrash studio learning, in this article we will clear up one of the most common SQL Server interview questions — what is the difference between UNION and UNION ALL? It sounds simple, but many candidates miss one small detail that can change the answer completely. We will use one tiny example, clean tables, and plain English to make it stick for life.

Quick Definition

Both UNION and UNION ALL are used to combine the results of two SELECT statements into a single result set. The only difference is how they treat duplicate rows.

UNION removes duplicate rows automatically. UNION ALL keeps every single row from both queries, including duplicates. That's the whole story — but let's see it with a clear example.

The Sample Tables

Imagine a small shop that tracks orders from two places — OnlineOrders and StoreOrders.


-- Table 1
CREATE TABLE OnlineOrders (Product VARCHAR(50), Amount INT);
INSERT INTO OnlineOrders VALUES
('Laptop', 55000),
('Headphones', 1500),
('Keyboard', 2200);

-- Table 2
CREATE TABLE StoreOrders (Product VARCHAR(50), Amount INT);
INSERT INTO StoreOrders VALUES
('Mouse', 800),
('Headphones', 1500),
('Monitor', 12000);

Notice that Headphones at 1500 appears in both tables. This is our duplicate — and the key to understanding the difference.

UNION: Combine and Remove Duplicates


SELECT Product, Amount FROM OnlineOrders
UNION
SELECT Product, Amount FROM StoreOrders;

Result — 5 rows:

Product	Amount
Laptop	55000
Headphones	1500
Keyboard	2200
Mouse	800
Monitor	12000

Headphones appears only once, even though it was in both tables. UNION silently removed the duplicate.

UNION ALL: Combine and Keep Everything


SELECT Product, Amount FROM OnlineOrders
UNION ALL
SELECT Product, Amount FROM StoreOrders;

Result — 6 rows:

Product	Amount
Laptop	55000
Headphones	1500
Keyboard	2200
Mouse	800
Headphones	1500
Monitor	12000

Headphones now appears twice — once from each table. Nothing is removed.

Quick Comparison

Feature	UNION	UNION ALL
Combines two SELECTs	Yes	Yes
Removes duplicates	Yes	No
Performance	Slower (extra step to remove duplicates)	Faster (just concatenates)
Default sort	Often sorted while de-duplicating	Order preserved as-is
When to use	When duplicates must be removed	When every row matters (logs, totals)

The Rules Both of Them Follow

Whether you use UNION or UNION ALL, both queries must follow the same two rules:

  Rule 1: Each SELECT must return the same number of columns.

  Rule 2: The matching columns must have compatible data types.

  If you break either rule, SQL Server will throw an error.

When to Use Which

Use UNION when...

You want a clean, de-duplicated list. Good examples: merging two customer lists, combining unique product catalogs, or listing distinct email addresses from multiple sources.

Use UNION ALL when...

You need every row, even duplicates. Good examples: joining monthly sales logs, summing totals across regions, or appending audit records where each event counts. UNION ALL is also much faster because SQL Server does not run the extra de-duplication step.

Performance tip: If you know your two queries cannot produce duplicates, always prefer UNION ALL. Running UNION on large tables just to "be safe" wastes a lot of CPU and memory.

Common Mistakes

Mistake 1: Different column counts. If one SELECT has 3 columns and the other has 2, you get an error. Always align both sides.

Mistake 2: Mismatched data types. Combining VARCHAR with INT in the same position can cause silent conversions or errors. Match types carefully.

Mistake 3: Using UNION when UNION ALL would do. Many beginners use UNION everywhere "just in case". On big tables this is slow — switch to UNION ALL when duplicates are impossible or acceptable.

Mistake 4: Expecting a specific order. Neither UNION nor UNION ALL guarantees any order. If you need a specific order, add an ORDER BY at the very end.


SELECT Product, Amount FROM OnlineOrders
UNION ALL
SELECT Product, Amount FROM StoreOrders
ORDER BY Amount DESC;

The Interview Answer

If an interviewer asks "What is the difference between UNION and UNION ALL?":

"Both combine the results of two SELECT statements into one result set. UNION removes duplicate rows automatically, while UNION ALL keeps every row — including duplicates. Because UNION ALL skips the de-duplication step, it is also noticeably faster on large tables."

Short, clear, and exactly what they want to hear.

Summary

UNION and UNION ALL both stitch two result sets together. The one-line difference: UNION hides duplicates, UNION ALL keeps them. Both need the same number of columns and compatible data types on each side.

Reach for UNION when you need a clean, distinct list. Reach for UNION ALL when every row matters or when speed is important. Remembering this single idea will give you a confident, correct answer to this interview question every single time.

Concept	Key Takeaway
UNION	Combines + removes duplicates
UNION ALL	Combines + keeps duplicates
Performance	UNION ALL is faster
Column rule	Same number of columns on both sides
Type rule	Compatible data types column by column
Ordering	Not guaranteed — add ORDER BY at the end
Interview line	"UNION removes duplicates, UNION ALL does not"

License Key