A full guide to `UNION` in MySQL

• Understanding the UNION Operator
• The UNION ALL Operator
• Practical Examples of UNION
• Ordering UNION Results
• UNION Inside a Derived Table
• Performance Considerations

In MySQL, one powerful feature that often goes underutilized is the UNION operator. This operator allows the combination of two or more SELECT statements into a single result set. It has various uses and can prove to be incredibly useful when dealing with multiple tables or views in your database. This article will provide a comprehensive guide to the UNION operator, its variations, and its usage in MySQL.

Before we begin it might be worth familiarising yourself with the documentation on UNION. Please also be aware that this guide is focussing on MySQL 8.0, always ensure you're checking the right version of MySQL documentation for your use case.

¶Understanding the UNION Operator

The UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of columns. The corresponding columns must also have similar data types.

Here is the basic syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

¶The UNION ALL Operator

Unlike UNION, the UNION ALL operator does not remove duplicate rows. It simply combines the results of two or more SELECT statements. This operator is often faster than UNION, as it does not carry the additional overhead of sorting the result set to remove duplicates.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

¶Practical Examples of UNION

Assume we have two tables, OrdersA and OrdersB, representing orders from two different regions. To get a combined list of all order IDs from both regions, we could use the UNION operator:

SELECT OrderID FROM OrdersA
UNION
SELECT OrderID FROM OrdersB;

This query would return a list of unique OrderIDs from both tables.

¶Ordering UNION Results

You can use the ORDER BY clause to sort the results of a UNION. The ORDER BY clause should come at the end of the query:

SELECT OrderID FROM OrdersA
UNION
SELECT OrderID FROM OrdersB
ORDER BY OrderID;

This query will return a sorted list of unique OrderIDs from both tables.

¶UNION Inside a Derived Table

You can use the UNION operator inside a derived table (a subquery in the FROM clause) to consolidate data from multiple tables into a single temporary table. Here's an example:

SELECT CustomerName FROM 
(
  SELECT CustomerName FROM CustomersA
  UNION
  SELECT CustomerName FROM CustomersB
) AS AllCustomers
ORDER BY CustomerName;

This query will create a temporary table AllCustomers that combines the customer names from CustomersA and CustomersB, and then retrieves these names in alphabetical order.

¶Performance Considerations

While UNION is a powerful operator, it's important to note that it can be expensive in terms of performance, particularly when dealing with large datasets. This is because UNION involves sorting and comparing the results to remove duplicates. Wherever possible, use UNION ALL if duplicates aren't an issue, as it can be faster.