11.3 UNION / UNION ALL

Level: 11 | Version: v1.1 | Author: Meptrasoft

Overview

UNION and UNION ALL combine the results of two or more SELECT queries into one result.

Operator	Purpose
`UNION`	Combines results and removes duplicates
`UNION ALL`	Combines results and keeps duplicates

UNION → Combine + Remove duplicates UNION ALL → Combine + Keep duplicates

UNION vs UNION ALL — Combine and Deduplicate Result Sets — Figure 1: UNION combines result sets and removes duplicate rows, while UNION ALL combines result sets without removing duplicates.

Practice Tables

This topic uses two tables:

Employees

name	department	salary
Alice	Marketing	65000
Bob	Sales	70000
Carol	Engineering	80000
John	HR	55000

Contractors

name	department	salary
David	Marketing	60000
Carol	Engineering	80000
John	HR	55000
Alice	Marketing	65000
Eva	Sales	68000
Bob	Sales	70000

UNION

UNION combines the results of two SELECT statements and removes duplicate rows.

SELECT name, department, salary
FROM employees

UNION

SELECT name, department, salary
FROM contractors;

The identical rows for Alice, Bob, Carol, and John are returned only once.

UNION → Duplicates removed

UNION ALL

UNION ALL combines the results and keeps every row.

SELECT name, department, salary
FROM employees

UNION ALL

SELECT name, department, salary
FROM contractors;

The rows that appear in both tables remain duplicated in the result.

UNION ALL → Duplicates kept

Important Rule

The SELECT statements used with UNION or UNION ALL must return the same number of columns, with compatible data types in corresponding positions.

For example:

SELECT name, salary
FROM employees

UNION

SELECT name, salary
FROM contractors;

is valid because both queries return two compatible columns.

UNION vs UNION ALL

`UNION`	`UNION ALL`
Removes duplicate rows	Keeps duplicate rows
Usually requires duplicate elimination work	Usually avoids duplicate-removal work
Result can contain fewer rows	Result contains all rows from both results

Use UNION when duplicates should be removed. Use UNION ALL when every row should be preserved.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
UNION combines tables permanently	It combines query result sets
UNION ALL removes duplicates	It keeps duplicates
The SELECTs can return different numbers of columns	They must return the same number of compatible columns
UNION compares only the first column	Duplicate elimination considers the entire selected row
UNION ALL is always slower	It avoids duplicate-removal work and can often be more efficient

Placement Quick Points

UNION
→ COMBINE + REMOVE DUPLICATES

UNION ALL
→ COMBINE + KEEP DUPLICATES

UNION combines result sets and removes duplicate rows.
UNION ALL combines result sets and keeps duplicates.
Corresponding SELECT statements must return the same number of compatible columns.
Duplicate comparison considers the complete result row.
UNION ALL is commonly preferred when duplicate removal is not required.

Interview Questions

What is UNION?

UNION combines the results of multiple SELECT queries and removes duplicate rows.

What is UNION ALL?

UNION ALL combines the results and keeps duplicate rows.

What is the main difference?

UNION     → Remove duplicates
UNION ALL → Keep duplicates

What requirement must SELECT statements satisfy?

They must return the same number of columns, with compatible data types in corresponding positions.

Which is generally faster, UNION or UNION ALL?

UNION ALL is often faster because it does not need to perform duplicate elimination.

Does UNION combine tables?

No. It combines the results of SELECT queries into one result set.

Practice & Hands-On Exercises

Using employees and contractors:

Write a UNION query:

SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

Write a UNION ALL query:

SELECT name, department, salary FROM employees
UNION ALL
SELECT name, department, salary FROM contractors;

Compare the number of rows returned.
Identify which rows are duplicates.
Explain why UNION ALL can be more efficient.
Write two compatible SELECT queries and combine them using UNION.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

UNION
→ COMBINE
→ REMOVE DUPLICATES

UNION ALL
→ COMBINE
→ KEEP DUPLICATES

UNION combines result sets and removes duplicates, while UNION ALL combines result sets without removing duplicates.

End of UNION / UNION ALL