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 statements into a single result set, including duplicates.
⚠️ Unlike
UNION, it does not remove duplicate rows.
🔹 Syntax
Rules:
-
Each
SELECTmust have the same number of columns. -
Corresponding columns must have similar data types.
-
Column names in the first SELECT are used as the result set column names.
🧠 Example Tables
Students2024
| id | name |
|---|---|
| 1 | John |
| 2 | Emma |
| 3 | Raj |
Students2025
| id | name |
|---|---|
| 4 | Sara |
| 5 | Aman |
| 2 | Emma |
🔹 SQL Query: Combine Students Using UNION ALL
🔹 Result
| name |
|---|
| John |
| Emma |
| Raj |
| Sara |
| Aman |
| Emma |
✅ Notice:
Emmaappears twice becauseUNION ALLincludes duplicates.All rows from both tables are combined.
🔹 Difference Between UNION and UNION ALL
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Included |
| Performance | Slightly slower | Faster |
| Use Case | Unique values | Keep all rows |
🔹 Key Points
-
Combines two or more SELECT queries vertically.
-
Preserves all duplicates.
-
Faster than UNION because no duplicate removal is performed.
-
Column count and data types must match across SELECT statements.
