SQL Union All

The SQL 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 ALL 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

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:

SQL UNION ALL Example

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

Example

 SELECT Country FROM Customers
UNION ALL
SELECT Country FROM Suppliers

 ORDER BY Country;

SQL UNION ALL With WHERE

Here we add a WHERE clause to return all the German cities 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;