MySQL UNION ALL

The MySQL UNION ALL Operator

The UNION ALL operator is used to combine the result-set of two or more SELECT statements.

The UNION ALL operator includes all rows from each statement, including any duplicates.

Requirements for UNION ALL:

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

UNION ALL Syntax

While the UNION operator removes duplicate values by default, the UNION ALL includes duplicate values:

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

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.

Demo Database

Below is a selection from the "Customers" table:

And a selection from the "Suppliers" table:

MySQL UNION ALL Example

The following SQL returns all the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example

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

MySQL UNION ALL With WHERE

The following SQL returns all the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example

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