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

CustomerID	CustomerName	Country
1	John	USA
2	Sarah	Germany
3	Mike	UK

Suppliers Table

SupplierID	SupplierName	Country
101	ABC Supplies	Germany
102	XYZ Traders	USA
103	DEF Imports	Canada

Query

SELECT Country
FROM Customers
UNION
SELECT Country
FROM Suppliers;

Result

Country
USA
Germany
UK
Canada

• 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

Country
USA
Germany
UK
Germany
USA
Canada

• 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

SaleID	Region	Amount
1	North	1000
2	North	1200

SouthSales Table

SaleID	Region	Amount
1	South	800
2	South	950

Query

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

Result

SaleID	Region	Amount
1	North	1000
2	North	1200
1	South	800
2	South	950

---

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

Country
Canada
Germany
UK
USA

---

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.

---