Union in MySql
UNION in MySQL
The UNION operator in MySQL is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows by default.
1. Syntax
SELECT column1, column2 FROM table1UNIONSELECT column1, column2 FROM table2;
🔹 Rules for Using UNION:
✅ Both SELECT statements must have the same number of columns.
✅ The columns must have compatible data types.
✅ By default, UNION removes duplicate rows.
2. Example Tables
📌 Table: employees_us
| emp_id | name | country |
|---|---|---|
| 1 | Alice | USA |
| 2 | Bob | USA |
📌 Table: employees_uk
| emp_id | name | country |
|---|---|---|
| 3 | Charlie | UK |
| 4 | Bob | UK |
3. Using UNION
SELECT name, country FROM employees_usUNIONSELECT name, country FROM employees_uk;
✅ Result (Duplicates Removed):
| name | country |
|---|---|
| Alice | USA |
| Bob | USA |
| Charlie | UK |
| Bob | UK |
🔹 Bob appears only once because UNION removes duplicates.
4. Using UNION ALL (Keeps Duplicates)
SELECT name, country FROM employees_usUNION ALLSELECT name, country FROM employees_uk;
✅ Result (Duplicates Kept):
| name | country |
|---|---|
| Alice | USA |
| Bob | USA |
| Charlie | UK |
| Bob | UK |
🔹 Here, Bob appears twice because UNION ALL does not remove duplicates.
5. Using ORDER BY with UNION
SELECT name, country FROM employees_usUNIONSELECT name, country FROM employees_ukORDER BY name ASC;
✅ Sorted Result:
| name | country |
|---|---|
| Alice | USA |
| Bob | USA |
| Bob | UK |
| Charlie | UK |
🔹 The ORDER BY must be at the end of the UNION query.
6. Using UNION with Different Conditions
SELECT name, country FROM employees_us WHERE country = 'USA'UNIONSELECT name, country FROM employees_uk WHERE country = 'UK';
✅ Filters employees by country before combining results.
Key Takeaways
✅ UNION combines results from multiple SELECT queries.
✅ Duplicates are removed by default (use UNION ALL to keep them).
✅ The number of columns and their data types must match.
✅ Use ORDER BY at the end of the final query.
