SQL UNION

Videos Discussion

In real-world databases, data is often spread across:

Multiple tables

Multiple conditions

Multiple time ranges

Sometimes, instead of joining tables side-by-side, we need to stack results vertically.

For example:

Active users + inactive users

Current employees + former employees

Orders from different years

Data from archive tables

This is where UNION and UNION ALL are used.

What Are UNION and UNION ALL?

Both UNION and UNION ALL:

Combine the result of two or more SELECT queries

Stack rows one below another

Require same number of columns with compatible data types

But they differ in how duplicates are handled.

Syntax

SELECT column_list FROM table1
UNION / UNION ALL
SELECT column_list FROM table2;

Column count and order must match.

Example: Table of online students and offline students

online_students

student_name
Amit
Neha
Rahul

offline_students

student_name
Neha
Priya
Karan

UNION – Removes Duplicate Rows

How UNION Thinks

Combine results, but remove duplicates.

UNION performs a DISTINCT operation automatically.

Example

SELECT student_name FROM online_students
UNION
SELECT student_name FROM offline_students;

Output

student_name
Amit
Neha
Rahul
Priya
Karan

Neha appears only once

UNION ALL – Keeps Everything

How UNION ALL Thinks

Combine results exactly as they are.

No duplicate removal, No additional processing

Example

SELECT student_name FROM online_students
UNION ALL
SELECT student_name FROM offline_students;

Output

student_name
Amit
Neha
Rahul
Neha
Priya
Karan

Neha appears twice

Why UNION Exists

UNION is useful when:

Duplicate data should not exist

You want clean, unique results

Data correctness matters more than performance

Examples

Unique customer list

Unique email IDs

Unique registered users

Why UNION ALL Exists

UNION ALL is used when:

Duplicate data is meaningful

Performance is critical

You want raw data

Examples

Transaction logs

Sales records

Attendance entries

Performance Difference

Aspect	UNION	UNION ALL
Duplicate removal	Yes	No
Sorting internally	Yes	No
Performance	Slower	Faster
Data accuracy	Clean	Raw

UNION ALL is always faster because SQL does no duplicate checking.

Example: Employee Records

current_employees

emp_name
Amit
Neha

resigned_employees

emp_name
Neha
Rahul

Scenario - 1: Get Unique Employee Names

SELECT emp_name FROM current_employees
UNION
SELECT emp_name FROM resigned_employees;

Use UNION (clean list)

Scenario - 2: Get Complete History

SELECT emp_name FROM current_employees
UNION ALL
SELECT emp_name FROM resigned_employees;

Use UNION ALL (historical data)

ORDER BY with UNION

Incorrect

SELECT name FROM table1 ORDER BY name
UNION
SELECT name FROM table2;

Correct

SELECT name FROM table1
UNION
SELECT name FROM table2
ORDER BY name;

Difference Between UINION and JOIN

UNION	JOIN
Vertical merge	Horizontal merge
Same structure	Related tables
Adds rows	Adds columns

When NOT to Use UNION

When tables have relationships - use JOIN

When column structure differs

When combining unrelated data blindly

Two Minute Drill

UNION removes duplicates
UNION ALL keeps duplicates
UNION ALL is faster
Column count must match
ORDER BY goes at the end
Use UNION for clean data
Use UNION ALL for raw data