MySQL UNION ALL Operator

MySQL UNION ALL Operator

The UNION ALL operator in MySQL is used to combine the result sets of two or more SELECT queries and include all rows, even duplicates.

•  Think of it as stacking results vertically without removing duplicates.

What is UNION ALL?

• Combines multiple SELECT results

• Keeps duplicate rows

• Faster than UNION (no duplicate check)

 All SELECT statements must have:

• Same number of columns

• Compatible data types

• Same column order

Basic Syntax

Simple Example

•  Returns all names, including duplicates

UNION vs UNION ALL (Very Important)

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 checking. UNION ALL is faster because it skips duplicate removal.

UNION ALL with Multiple Columns

All SELECT statements must return the same number of columns in the same order. Corresponding columns must have compatible data types.

UNION ALL with WHERE Clause

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

UNION ALL with ORDER BY

ORDER BY is applied once at the end of the complete UNION query. Column aliases or column positions are used for sorting.

UNION ALL with Different Table Names (Aliases)

Table aliases help improve readability and avoid confusion. Aliases do not affect UNION logic.

UNION ALL with Aggregate Queries

Aggregate functions can be used in UNION queries. Each SELECT performs its own aggregation before UNION ALL combines the results.

Common Errors & Fixes

Interview Questions & MCQs (Very Important)

Q1. What does UNION ALL do?

A) Joins tables
B) Removes duplicates
C) Combines results including duplicates
D) Sorts data

Answer: C

Q2. Which is faster?

A) UNION
B) UNION ALL

Answer: B

Q3. Can UNION ALL remove duplicates?

A) Yes
B) No

Answer: B

Q4. How many ORDER BY clauses are allowed?

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

Answer: B

Q5. Can UNION ALL work with more than two SELECT statements?

A) Yes
B) No

Answer: A

Q6. Column count rule in UNION ALL?

A) Any
B) Same count & compatible types
C) Same table
D) Same names only

Answer: B

Real-Life Use Cases

•  Combining yearly data
•  Merging logs from multiple tables
• Appending partitioned tables
•  Reporting across periods

Summary

• UNION ALL combines SELECT results with duplicates

• Faster than UNION

• Requires same column count & compatible types

• ORDER BY goes at the end

• Essential for analytics & interviews