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