👉What is MySQL UNION?
MySQL UNION: The UNION operator in MySQL is used to combine results from multiple SELECT queries into a single result set. It removes duplicates by default unless specified otherwise.
Key Rules for Using UNION:
✔ The number of columns in each SELECT query must be the same.
✔ The data types of corresponding columns must be compatible.
✔ By default, MySQL removes duplicates unless UNION ALL is used.
👉Types of MySQL UNION Queries
1. UNION (Removes Duplicates – Default Behavior)
The UNION command returns only distinct records from multiple SELECT statements.
Syntax:
sql
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
Example:
Suppose we have two tables: members and movies. We want to combine membership_number and full_names from members with movie_id and title from movies, while eliminating duplicate entries.
sql
SELECT membership_number, full_names FROM members
UNION
SELECT movie_id, title FROM movies;
Output:
| membership_number | full_names |
| 1 | Janet Jones |
| 2 | Janet Smith Jones |
| 3 | Robert Phil |
| 5 | Leonard Hofstadter |
| 16 | 67% Guilty |
| 6 | Angels and Demons |
Note: Since UNION removes duplicates by default, only unique rows appear in the result.
2. UNION ALL (Includes Duplicates)
MySQL UNION: The UNION ALL command keeps duplicate values in the final result set.
Syntax:
sql
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
Example:
If we want to include duplicates while merging the members and movies tables:
sql
SELECT membership_number, full_names FROM members
UNION ALL
SELECT movie_id, title FROM movies;
Output (includes duplicates if any exist):
| membership_number | full_names |
| 1 | Janet Jones |
| 2 | Janet Smith Jones |
| 2 | Forgetting Sarah Marshal |
| 3 | Robert Phil |
| 3 | X-Men |
| 5 | Leonard Hofstadter |
| 5 | Daddy’s Little Girls |
✅ Use UNION ALL when you need all records, including duplicates.
👉Why Use UNION Instead of Multiple Queries?
🔹MySQL UNION: Combines Data from Similar Tables: Useful when data is stored across multiple tables.
🔹 Improves Query Efficiency: Eliminates the need for separate queries and complex joins.
🔹 Useful for Merging Reports: Great for generating combined reports from different sources.
Example Use Case:
Imagine an e-commerce system where customer orders are stored in multiple tables due to historical reasons. Instead of writing separate queries, you can use UNION to consolidate the data.
👉Best Practices for Using MySQL UNION
✅ Ensure each SELECT statement retrieves the same number of columns.
✅ Use UNION when you need only unique records.
✅ Use UNION ALL when you need all records, including duplicates.
✅ Optimize performance by indexing the columns used in the query.
👉Conclusion
MySQL UNION is a powerful tool for merging data from multiple SELECT queries into a single result set. By understanding UNION vs. UNION ALL, you can efficiently retrieve and organize your database records.
Got any questions? Drop them in the comments!
This version is SEO-optimized, structured for readability, and focused on user engagement. Let me know if you need further refinements!
