MySQL UNION

MySQL UNION Clause

The UNION operator in MySQL is used to combine the result sets of two or more SELECT statements into a single result set, eliminating duplicate rows by default. The combined result set will have columns matching the first SELECT statement.

Syntax

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

• UNION: Combines the results of two or more SELECT statements.
• UNION ALL: Includes duplicate rows in the result set.

Key Rules for Using UNION

1. Column Alignment:

   • Each SELECT statement must return the same number of columns.
   • The data types of the columns in corresponding positions must be compatible.

2. Order of Columns:

   • Column names in the result set are taken from the first SELECT statement.

3. Default Behavior:

   • By default, UNION eliminates duplicate rows. Use UNION ALL to include duplicates.

Examples

Example 1: Basic UNION

Goal: Combine data from two tables: customers_usa and customers_canada.

SELECT customer_id, first_name, last_name
FROM customers_usa
UNION
SELECT customer_id, first_name, last_name
FROM customers_canada;

Result:

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
| 1           | John       | Smith     |
| 2           | Alice      | Johnson   |
| 3           | Bob        | Brown     |
+-------------+------------+-----------+

Example 2: UNION ALL (Retain Duplicates)

SELECT customer_id, first_name, last_name
FROM customers_usa
UNION ALL
SELECT customer_id, first_name, last_name
FROM customers_canada;

Result:

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
| 1           | John       | Smith     |
| 2           | Alice      | Johnson   |
| 1           | John       | Smith     |
| 3           | Bob        | Brown     |
+-------------+------------+-----------+

Example 3: Adding Filters

Goal: Retrieve customers with IDs less than 5 from one table and IDs greater than 10 from another.

SELECT customer_id, first_name
FROM customers_usa
WHERE customer_id < 5
UNION
SELECT customer_id, first_name
FROM customers_canada
WHERE customer_id > 10;

Example 4: Sorting the Result Set

To sort the combined results, use ORDER BY after the final SELECT statement.

SELECT first_name, last_name
FROM customers_usa
UNION
SELECT first_name, last_name
FROM customers_canada
ORDER BY last_name ASC;

Example 5: Combining Data from the Same Table

Goal: Retrieve a list of employees, combining different job titles into a single result set.

SELECT employee_id, first_name, last_name, 'Manager' AS role
FROM employees
WHERE role = 'Manager'
UNION
SELECT employee_id, first_name, last_name, 'Engineer' AS role
FROM employees
WHERE role = 'Engineer';

UNION vs. UNION ALL

Feature	UNION	UNION ALL
Duplicates	Removes duplicate rows	Keeps duplicate rows
Performance	Slightly slower due to duplicate checks	Faster as no duplicate checks
Use Case	Unique rows only	All rows, including duplicates

Common Errors and How to Fix Them

1. Mismatch in Number of Columns:

   • Error: #1222 - The used SELECT statements have a different number of columns
   • Fix: Ensure all SELECT statements have the same number of columns.

   SELECT customer_id, first_name
   FROM customers_usa
   UNION
   SELECT customer_id, first_name, last_name -- Incorrect
   FROM customers_canada;
   

2. Data Type Incompatibility:

   • Ensure corresponding columns in the SELECT statements have compatible data types.

3. Using ORDER BY in Intermediate Queries:

   • Error: ORDER BY must be at the end of the UNION query.
   • Fix: Move ORDER BY to the final query.

Performance Tips

1. Use UNION ALL if you do not need to remove duplicates—it avoids the overhead of sorting and comparing rows.
2. Add indexes on columns used in the SELECT and WHERE clauses to improve performance.
3. Filter rows in individual SELECT statements before applying UNION.

Conclusion

The UNION operator is a powerful tool for combining datasets from multiple queries into a unified result set. By using variations like UNION and UNION ALL, you can control the inclusion of duplicates based on your needs. With proper optimization, it becomes a vital query mechanism in MySQL