MySQL UNION: The UNION operator in MySQL allows you to combine the results of multiple SELECT queries into a single result set. This is useful when retrieving data from multiple tables while ensuring consistency in the output.
Unlike JOINs, which merge columns from different tables, UNION merges rows while maintaining a consistent column structure.
📌 What is MySQL UNION?
MySQL UNION: The UNION operator merges the output of two or more SELECT queries into a single dataset. It automatically removes duplicate rows unless specified otherwise.
Basic Syntax:
sql
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
Key Rules:
✔️ The number of columns must be the same in both queries.
✔️ The data types of corresponding columns must match.
✔️ By default, UNION removes duplicates (similar to DISTINCT).
📌 Using UNION vs. UNION ALL in MySQL
MySQL UNION: MySQL provides two variations of the UNION operator:
| Operator | Behavior | Duplicates Removed? |
| UNION | Combines multiple SELECT queries | ✅ Yes (Default) |
| UNION ALL | Combines multiple SELECT queries | ❌ No (Includes all duplicates) |
Example 1: UNION (Removing Duplicates)
MySQL UNION: Scenario: Suppose we have two tables, customers and suppliers, both storing contact details. We want to retrieve a unique list of all company names.
sql
SELECT company_name FROM customers
UNION
SELECT company_name FROM suppliers;
✔️ How it Works:
- The query combines company_name from both tables.
- Duplicate names are automatically removed
Example 2: UNION ALL (Keeping Duplicates)
Scenario: Now, we want to retrieve all company names, including duplicates.
sql
SELECT company_name FROM customers
UNION ALL
SELECT company_name FROM suppliers;
✔️ How it Works:
- This time, all company names (even duplicates) are included in the result.
- UNION ALL improves performance since MySQL doesn’t check for duplicates.
📌Why Use MySQL UNION?
Common Use Cases:
🔹 Combining data from different tables that store similar information.
🔹 Fixing database design issues when multiple tables store overlapping data.
🔹 Merging query results when filtering records from various sources.
Example: If your system mistakenly stores active users and inactive users in separate tables, you can use UNION to fetch all users in a single result set.
sql
SELECT user_id, full_name FROM active_users
UNION
SELECT user_id, full_name FROM inactive_users;
📌 MySQL UNION with Practical Examples
Example 3: Combining Membership & Movie Data
Scenario:
The MyFlixDB database contains:
- Members Table: Stores membership_number and full_names.
- Movies Table: Stores movie_id and title.
We want to merge members’ full names with movie titles into a single list.
sql
SELECT membership_number, full_names FROM members
UNION
SELECT movie_id, title FROM movies;
✔️ How it Works:
- Merges the two datasets into a single list.
- Ensures all results fit within two columns.
Sample Output:
| membership_number | full_names |
| 1 | Janet Jones |
| 2 | Janet Smith Jones |
| 3 | Robert Phil |
| 4 | Gloria Williams |
| 6 | Angels and Demons |
| 7 | Da Vinci Code |
| 9 | Honey Mooners |
📌 UNION vs. JOIN – What’s the Difference?
| Feature | UNION | JOIN |
| Combines | Rows (Vertical Merge) | Columns (Horizontal Merge) |
| Duplicates | Removes (Default) | Keeps |
| Performance | Slightly Slower | Faster for related data |
| Usage | Fetching similar records | Linking related tables |
When to Use What?
- Use UNION when merging data from separate tables with similar structure.
- Use JOIN when linking related tables based on a common column.
📌Performance Considerations – Optimizing UNION Queries
Best Practices:
✔️ Use UNION ALL whenever possible (to avoid extra sorting operations).
✔️ Ensure all SELECT queries use indexed columns for faster retrieval.
✔️ Avoid unnecessary columns in the SELECT statements.
📌 Summary – Key Takeaways
🔹 MySQL UNION merges multiple SELECT queries into a single result set.
🔹 UNION removes duplicates, while UNION ALL keeps them.
🔹 The number of columns and data types must match in all SELECT queries.
🔹 JOINs are faster than UNION when combining related tables.
🔹 Always optimize UNION queries to improve database performance.
By mastering UNION in MySQL, you can effectively combine datasets and write efficient queries!
