SQL Union & Union All

The UNION and UNION ALL operators are used to combine the result-sets of two or more separate SELECT statements into a single output.

While a JOIN combines columns from different tables side-by-side, a UNION appends rows from different tables on top of each other vertically.

The Critical Difference: UNION vs. UNION ALL

Feature	UNION	UNION ALL
Duplicate Rows	Removes duplicates. It filters the final dataset to ensure each row is completely unique.	Keeps all duplicates. It blindly appends every single record returned by the queries.
Performance Speed	Slower. The database engine has to sort and scan the entire combined dataset to remove duplicates.	Blazing Fast. It simply appends the data without any internal sorting or parsing.
Memory Usage	Higher. Requires extra temporary memory allocation to perform duplicate checks.	Minimal. It immediately streams rows directly to the output.

Strict Structural Rules for Syntax

To successfully combine queries using either operator, your statements must follow three mandatory rules:

Each SELECT statement must have the exact same number of columns.
The columns must be in the same chronological order.
The columns must have compatible data types (e.g., you cannot stack a text column on top of an integer column).

Basic Syntax Blueprint

Using UNION (Unique Rows)

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

If a city like "Mumbai" contains both a customer and a supplier, it will display exactly once in the final output.

Using UNION ALL (All Rows)

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

If "Mumbai" appears in both tables, it will display twice in the output.

Real-World Combined Example

Imagine you are building a master mailing dashboard pulling contact details across separate leads and subscribers tables. You also want to clear out any duplicate emails to avoid spamming contacts, while sorting the final output alphabetically.

-- Step 1: Query the first dataset
SELECT first_name, email, 'Lead' AS account_type
FROM marketing_leads

UNION -- Step 2: Combine and filter out duplicates

-- Step 3: Query the second dataset
SELECT first_name, email, 'Subscriber' AS account_type
FROM user_subscribers

-- Step 4: Apply sorting at the very end
ORDER BY email ASC;

⚠️ Syntax Note: You can only place a single ORDER BY clause at the very absolute end of the complete script. It will sort the final, combined results.

Production Rule of Thumb

Always default to using UNION ALL if you already know that the datasets have completely distinct values (for example, combining archive data from orders_2025 and orders_2026). Avoiding UNION prevents your database engine from running heavy sorting operations on large scales of data.