MySQL UNION Operator: A Complete Guide with Examples

What is the UNION Operator in MySQL?

The UNION operator in MySQL is used to combine the results of two or more SELECT statements into a single result set. By default, it removes duplicate rows from the final output.

Key Features of UNION

    1. Combines results from multiple SELECT queries.
    2. Removes duplicates unless UNION ALL is used.
    3. Requires the same number of columns in all SELECT queries.
    4. Corresponding columns must have compatible data types.

    Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

For keeping duplicates:

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

Examples of UNION Operator

1. Combining Results from Two Tables

Retrieve all employee names from two different tables: full_time_employees and part_time_employees.

Query:

SELECT name FROM full_time_employees
UNION
SELECT name FROM part_time_employees;

Result:

+----------+
| name     |
+----------+
| Alice    |
| Bob      |
| Charlie  |
+----------+

2. Using UNION ALL

Include duplicate names in the result.

Query:

SELECT name FROM full_time_employees
UNION ALL
SELECT name FROM part_time_employees;

3. Combine Data with WHERE Clause

Retrieve employees working in “HR” or “Finance” from two tables.

Query:

SELECT name FROM full_time_employees WHERE department = 'HR'
UNION
SELECT name FROM part_time_employees WHERE department = 'Finance';

4. Combining with ORDER BY

Sort the combined results alphabetically.

Query:

SELECT name FROM full_time_employees
UNION
SELECT name FROM part_time_employees
ORDER BY name;

Best Practices for Using UNION

Ensure Column Count Matches

Ensure Column Count Matches
The number of columns and their order must be the same in all SELECT statements.

Use Compatible Data Types
Corresponding columns in the queries should have compatible types (e.g., VARCHAR with VARCHAR or INT with INT).

Optimize with UNION ALL
Use UNION ALL when you do not need to eliminate duplicates, as it is faster.

Combine with LIMIT for Performance
Limit the number of rows in each query before applying UNION.

Example:

SELECT name FROM full_time_employees LIMIT 10
UNION
SELECT name FROM part_time_employees LIMIT 10;

Index Columns Used in Queries
Proper indexing improves performance for large datasets.

MySQL Workbench Instructions

  • Open MySQL Workbench and connect to your database.
  • Write a SELECT query using the UNION operator in the SQL editor.
  • Click Execute to view the combined results.
  • Use the result grid to analyze and verify the output.

Common Issues and Troubleshooting

  • Mismatch in Column Count
    Ensure all SELECT statements have the same number of columns.

  • Incompatible Data Types
    Verify that corresponding columns have compatible data types to avoid errors.

  • Duplicate Handling
    If duplicates persist unexpectedly, check if you accidentally used UNION ALL instead of UNION.

  • Performance on Large Datasets
    Optimize queries using indexes and filters (WHERE clauses) before applying UNION.

[WpProQuiz 12]