SQL UNION Operator

The UNION operator is used to combine the results of two or more SELECT queries into a single result set. Instead of joining two tables side by side (like a JOIN), UNION stacks the results of multiple queries on top of each other — row by row.

Think of UNION like combining two separate attendance lists from two different classrooms into one master list.

Rules for Using UNION

• Each SELECT statement must have the same number of columns.
• The columns must have compatible data types (e.g., text with text, number with number).
• The column names in the final result are taken from the first SELECT statement.

The Reference Tables

OnlineStudents Table

OfflineStudents Table

Notice that Priya Mehta (ID 2) appears in both tables — this will demonstrate the difference between UNION and UNION ALL.

UNION — Removes Duplicates

UNION combines results from both queries and removes duplicate rows automatically.

Syntax

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Example

SELECT StudentName, City FROM OnlineStudents
UNION
SELECT StudentName, City FROM OfflineStudents;

Result:

Priya Mehta appears only once even though she is in both tables — UNION removed the duplicate row.

UNION ALL — Keeps Duplicates

UNION ALL combines results from both queries and keeps all rows, including duplicates. It is faster than UNION because it does not perform the extra step of removing duplicates.

SELECT StudentName, City FROM OnlineStudents
UNION ALL
SELECT StudentName, City FROM OfflineStudents;

Result:

This time Priya Mehta appears twice — once from each table.

UNION vs UNION ALL

UNION With Different Column Names

If the two queries have different column names, the result uses the column names from the first SELECT.

SELECT StudentName AS FullName, City AS Location FROM OnlineStudents
UNION
SELECT StudentName, City FROM OfflineStudents;

Result columns: FullName, Location (from the first query)

UNION With a WHERE Clause

Each individual SELECT in a UNION can have its own WHERE clause.

SELECT StudentName, City, 'Online' AS Mode FROM OnlineStudents
WHERE City = 'Delhi'

UNION

SELECT StudentName, City, 'Offline' AS Mode FROM OfflineStudents
WHERE City = 'Pune';

Result:

UNION With ORDER BY

ORDER BY applies to the entire combined result and should appear at the end of the last query.

SELECT StudentName, City FROM OnlineStudents
UNION
SELECT StudentName, City FROM OfflineStudents
ORDER BY StudentName ASC;

Practical Use Case: Combining Records From Two Time Periods

-- All students who joined in 2023 or 2024
SELECT StudentName, JoinDate FROM Students2023

UNION ALL

SELECT StudentName, JoinDate FROM Students2024

ORDER BY JoinDate;

Key Points to Remember

• Both SELECT statements must return the same number of columns with compatible types.
• UNION removes duplicates; UNION ALL keeps them.
• Use UNION ALL when duplicates are acceptable — it performs faster.
• ORDER BY goes at the very end and sorts the entire combined result.
• A constant value (like 'Online' AS Mode) can be added to each SELECT to identify the source of each row.

Summary

The UNION operator merges results from multiple SELECT queries into a single output by stacking rows. UNION removes duplicates while UNION ALL retains them. It is ideal for combining similar data from different tables, time periods, or filtered subsets into one unified report.