MySQL UNION Operator

MySQL UNION Operator

The UNION operator in MySQL is used to combine the result sets of two or more SELECT queries into a single result set, removing duplicate rows automatically.

Think of UNION as vertical merging + duplicate removal.

What is UNION?

Combines results of multiple SELECT statements
Eliminates duplicate rows
Returns a distinct set of records

Each SELECT must have:

Same number of columns
Compatible data types
Same column order

Basic Syntax

Simple Example

Returns unique student names only
Duplicate names appear once

UNION vs UNION ALL (Very Important Interview Topic)

UNION combines result sets and removes duplicate rows automatically. UNION ALL combines result sets and keeps all rows, including duplicates. UNION is slower because it performs duplicate elimination. UNION ALL is faster because no duplicate checking is done.

UNION with Multiple Columns

All SELECT statements must return the same number of columns. The column order and data types must be compatible.

UNION with WHERE Clause

Each SELECT can have its own WHERE clause. Filtering happens before the results are merged.

UNION with ORDER BY

ORDER BY is applied once at the end of the UNION query. You can use column names, aliases, or column positions.

UNION with Literal / Alias Columns

Literal values or aliases can be used to label rows. Aliases must match column positions across all SELECT statements.

UNION with Aggregate Queries

Each SELECT performs its aggregation separately. The aggregated results are then combined by UNION.

Common Errors & Fixes

Error	Cause	Fix
Column count mismatch	Different columns	Match column count
Data type mismatch	Incompatible types	Use CAST()
ORDER BY error	Placed before UNION	Move ORDER BY to end

UNION vs JOIN (Interview)

Feature	UNION	JOIN
Combines	Rows	Columns
Direction	Vertical	Horizontal
Relationship needed	No	Yes

Interview Questions & MCQs (Very Important)

Q1. What does UNION do?

A) Joins tables
B) Combines results removing duplicates
C) Sorts rows
D) Filters rows

Answer: B

Q2. Does UNION remove duplicates?

A) Yes
B) No

Answer: A

Q3. Which is faster?

A) UNION
B) UNION ALL

Answer: B

Q4. How many ORDER BY clauses are allowed in UNION?

A) One per SELECT
B) Only one at the end
C) None
D) Unlimited

Answer: B

Q5. Can UNION combine more than two SELECT statements?

A) Yes
B) No

Answer: A

Q6. Column rule for UNION?

A) Same names only
B) Same number & compatible types
C) Same table
D) Same primary key

Answer: B

Q7. Which removes duplicates automatically?

A) UNION
B) UNION ALL

Answer: A

Real-Life Use Cases

Merging yearly/partitioned data
Combining reports from multiple tables
Creating consolidated views
Removing duplicates across datasets

Summary

UNION combines multiple SELECT results
Automatically removes duplicate rows
Slower than UNION ALL
Requires same column count & compatible types
ORDER BY applies at the end
Very important for SQL exams & interviews

MySQL UNION Operator