UNION / UNION ALL – Stacking Results in SQL

UNION / UNION ALL – Stacking Results in SQL
(because sometimes, you need to combine two lists into one)

Quick Syntax

SELECT column1, column2
FROM table1

UNION -- or UNION ALL

SELECT column1, column2
FROM table2;

This tells SQL:
“Run both queries and combine their results. Use UNION to remove duplicates, or UNION ALL to keep everything.”

Context: Suppose You Have Two Tables of Sales

Let’s say you manage sales from two different stores, and each store keeps its own sales records in separate tables.

🛒 Table: store_a_sales

customer	amount
Alice	100
Bob	150
Carol	200

🛍️ Table: store_b_sales

customer	amount
Alice	100
Dave	300
Emma	180

Suppose You Need To…

…generate a single combined list of all sales from both stores.

Here’s how you’d do it.

Using UNION – Combine & Remove Duplicates

SELECT customer, amount
FROM store_a_sales

UNION

SELECT customer, amount
FROM store_b_sales;

Result:

customer	amount
Alice	100
Bob	150
Carol	200
Dave	300
Emma	180

Notice:
Alice appears only once, even though she exists in both tables. That’s because UNION removes duplicates by default.

Using UNION ALL – Combine & Keep Everything

SELECT customer, amount
FROM store_a_sales

UNION ALL

SELECT customer, amount
FROM store_b_sales;

Result:

customer	amount
Alice	100
Bob	150
Carol	200
Alice	100
Dave	300
Emma	180

Here, Alice shows up twice — once from each table. UNION ALL doesn’t remove duplicates.

Let’s Compare

Feature	UNION	UNION ALL
Duplicates	Removed	Kept as-is
Speed	Slower (filters dups)	Faster (no filtering)
Use case	Clean, unique list	Full raw data (including repeats)

Important Rules

• Both SELECT queries must have the same number of columns
• Columns must be in the same order and have compatible data types
• Use ORDER BY only after the last query:

SELECT ...
UNION
SELECT ...
ORDER BY column_name;

Recap

• Use UNION when you want no duplicates
• Use UNION ALL when you want everything, even if it’s repeated
• Great for combining results from different tables or queries
• Just remember: same number of columns, same structure