The SQL UNION Operator

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

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

UNION Syntax:

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

UNION ALL Syntax:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

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 Employee table

This employee table is used for examples:

demo table

Demo Supplier table

This supplier table is used for examples:

demo suplier table

SQL UNION Example

The following SQL statement returns the countries (only distinct values) from both the "employee" and the "supplier" table:

Example:

SELECT Country FROM employee
UNION SELECT Country FROM supplier
ORDER BY Country;

union 1 table

Note: If some employee or suppliers have the same country, each country will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement returns the Country (duplicate values also) from both the "employee" and the "Supplier" table:

Example:

SELECT Country FROM employee
UNION ALL SELECT Country FROM supplier
ORDER BY Country;

union 3 table

SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the "employee" and the "Supplier" table:

Example:

SELECT City,Country FROM employee
WHERE Country= 'Germany'
UNION
SELECT City,Country FROM supplier
WHERE Country= 'Germany'
ORDER BY City;

union where table

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the "employee" and the "supplier" table:

Example:

SELECT City,Country FROM employee
WHERE Country= 'Germany'
UNION ALL
SELECT City,Country FROM supplier
WHERE Country= 'Germany'
ORDER BY City;

union all where table