UNION Operator

UNION Operator in MySQL

Introduction
Combining results from multiple similar queries creates complete datasets.
The UNION operator in MySQL merges multiple SELECT statements vertically.
It is widely used for:

• Combining similar data sources
• Cross-table reporting
• Alternative condition results

What is the UNION Operator
UNION combines result sets from multiple SELECT queries into a single result.
Automatically removes duplicate rows (DISTINCT behaviour).

Basic Syntax

sql

SELECT column1, column2 FROM table1 

UNION 

SELECT column1, column2 FROM table2; 

Example

sql

SELECT name, marks FROM Students 

UNION 

SELECT name, score FROM Graduates; 

Combines students and graduates, removes duplicates.

UNION with WHERE Clauses
Filter each query before combining.

sql

SELECT name, city FROM Students WHERE marks > 90 

UNION 

SELECT name, city FROM Graduates WHERE score > 90; 

UNION Multiple Queries
Combine more than 2 SELECTs.

sql

SELECT name FROM Students WHERE city = 'Delhi.' 

UNION 

SELECT name FROM Graduates WHERE city = 'Mumba.i' 

UNION 

SELECT name FROM Alumni WHERE city = 'Bangalore'; 

UNION with ORDER BY
Sort applies to the final combined result.

sql

SELECT name, marks FROM Students 

UNION 

SELECT name, score FROM Graduates 

ORDER BY marks DESC; 

UNION Column Requirements
Same number of columns, compatible data types.

sql

-- Valid: same structure 

SELECT name, marks FROM Students 

UNION 

SELECT full_name, score FROM Graduates; 

UNION with Different Tables
Cross-table data merging.

sql

SELECT employee_id, salary FROM CurrentEmployees 

UNION 

SELECT id, amount FROM PastBonuses; 

UNION with Subqueries
Complex query combinations.

sql

SELECT city, AVG(marks) FROM Students GROUP BY city 

UNION 

SELECT city, AVG(score) FROM Graduates GROUP BY city; 

UNION with LIMIT
The limit applies to the final result only.

sql

SELECT name FROM Students WHERE marks > 90 

UNION 

SELECT name FROM Graduates WHERE score > 90 

ORDER BY name 

LIMIT 10; 

UNION Performance
Processes each SELECT independently.

sql

-- Each query optimized separately 

SELECT * FROM Students WHERE city = 'Delhi' 

UNION 

SELECT * FROM Graduates WHERE city = 'Mumbai'; 

Parentheses with UNION
Control complex combinations.

sql

(SELECT name FROM Students WHERE marks > 90 

 UNION 

 SELECT name FROM Graduates WHERE score > 90) 

INTERSECT 

SELECT name FROM Toppers; 

Execution Order with UNION
Each SELECT executes independently → UNION removes duplicates → ORDER BY → LIMIT

Performance Considerations

• Indexes work on individual SELECTs
• Large datasets: UNION ALL faster
• Sort buffer used for duplicate removal
• ORDER BY on the final result only

Important Notes

• UNION = UNION DISTINCT (removes duplicates)
• Same number/compatible columns required
• Column names from the first SELECT
• ORDER BY applies to the entire result
• NULL handling is consistent across queries

Example Scenario
Combine current and past high achievers:

sql

SELECT name, marks, 'Student' as category, city 

FROM Students 

WHERE marks >= 95 

UNION 

SELECT name, score, 'Graduate' as category, city 

FROM Graduates 

WHERE score >= 95 

ORDER BY marks DESC, name 

LIMIT 20;

Common Mistakes

• Mismatched column count
• Incompatible data types
• ORDER BY in individual SELECTs (ignored)
• Expecting UNION to work horizontally (use JOIN)
• Performance surprise vs UNION ALL

Key Points to Remember

• UNION combines SELECTs vertically, removes duplicates
• Same column structure required
• ORDER BY applies to the final result only
• Individual SELECTs optimized separately
• Use UNION ALL to keep duplicates
• Column names from the first SELECT