The UNION operator in MySQL is used to combine the result sets of two or more SELECT queries. It removes duplicate rows between the queries and outputs a single result set.

If you want to include duplicate rows, use UNION ALL instead.

Syntax

SELECT column_names
FROM table1
UNION
SELECT column_names
FROM table2;

• The columns in both SELECT queries must have:
  • The same number of columns.
  • Similar data types in corresponding columns.
  • The columns must be in the same order.

Use Cases

1. Combining results from multiple tables.
2. Merging results from different queries on the same table.

Example 1: Combine Customer and Supplier Locations

Suppose we have the following tables:

Customers Table

Suppliers Table

Query

SELECT Country
FROM Customers
UNION
SELECT Country
FROM Suppliers;

Result

• Explanation: Combines unique countries from both Customers and Suppliers.

Example 2: Include Duplicate Rows with UNION ALL

Query

SELECT Country
FROM Customers
UNION ALL
SELECT Country
FROM Suppliers;

Result

• Explanation: UNION ALL includes duplicates, so USA and Germany appear twice.

Example 3: Combine Sales Data

Suppose you have two tables representing sales in different regions:

NorthSales Table

SouthSales Table

Query

SELECT SaleID, Region, Amount
FROM NorthSales
UNION
SELECT SaleID, Region, Amount
FROM SouthSales;

Result

Using ORDER BY with UNION

You can apply ORDER BY to the entire result set, but it must come after the final UNION.

Query

SELECT Country
FROM Customers
UNION
SELECT Country
FROM Suppliers
ORDER BY Country;

Result

Key Points

1. Duplicates: UNION removes duplicates, while UNION ALL retains them.
2. Column Order: Both queries must return the same number of columns with compatible data types.
3. Performance: UNION may be slower than UNION ALL due to the removal of duplicates.

When to Use UNION vs. UNION ALL

• Use UNION when you want unique rows in the result set.
• Use UNION ALL when you want to retain all rows, including duplicates.