MySQL UNION Operator

The UNION operator in MySQL is used to combine the results of two or more SELECT queries into a single result set. It allows you to retrieve data from multiple tables or queries, but there are some important rules to follow:

1. Same number of columns: Every SELECT statement within the UNION must have the same number of columns.
2. Similar data types: The columns in each SELECT statement must have similar data types.
3. Same column order: The columns in each SELECT statement must appear in the same order.

UNION Syntax

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

The UNION operator by default returns distinct (unique) records.

UNION ALL Syntax

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

• The UNION ALL operator includes duplicate values in the result set.
• Use UNION ALL when you want to retain duplicates.

Important Notes

• The column names in the result set are usually taken from the first SELECT statement.
• If some rows in both queries are identical, UNION eliminates the duplicates. To retain duplicates, you use UNION ALL.

SQL UNION Example

This example retrieves the distinct cities from both the "Customers" and "Suppliers" tables:

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

• This query lists all unique cities that are either in the Customers or Suppliers table.

SQL UNION ALL Example

This example retrieves all cities (including duplicates) from both the "Customers" and "Suppliers" tables:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

• This query includes duplicate city names from both tables.

SQL UNION With WHERE

This example retrieves the distinct cities from both the "Customers" and "Suppliers" tables where the Country is "Germany":

SELECT City, Country FROM Customers
WHERE Country = 'Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country = 'Germany'
ORDER BY City;

• The query only includes cities from Germany, and duplicate city names are eliminated.

SQL UNION ALL With WHERE

This example retrieves the cities from both the "Customers" and "Suppliers" tables where the Country is "Germany", and includes duplicate cities:

SELECT City, Country FROM Customers
WHERE Country = 'Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country = 'Germany'
ORDER BY City;

• This query retains duplicates, showing all cities that belong to Germany from both tables.

Another UNION Example

This example retrieves a list of all customers and suppliers, and uses an alias to differentiate between the two types:

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

• Alias ('Customer' AS Type) is used to give a temporary name to the column.
• This query returns a list of contact names, cities, and countries, along with an additional column Type to distinguish whether the contact is a "Customer" or "Supplier".

Key Differences between UNION and UNION ALL

• UNION: Returns only distinct (unique) records, removing duplicates.
• UNION ALL: Returns all records, including duplicates.

The choice between UNION and UNION ALL depends on whether you want to remove duplicate values or keep them.