SQL - UNION ALL With WHERE

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the "Customers" and "Suppliers" tables:

Example

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

Try it Yourself »

SQL - UNION ALL Example

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the "Customers" and "Suppliers" tables:

Example

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

Try it Yourself »

SQL - UNION Example

The following SQL statement selects all the different cities (only distinct values) from the "Customers" and the "Suppliers" tables:

Example

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

Try it Yourself »

Note: UNION cannot be used to list ALL cities from the two tables. If several customers and suppliers share the same city, each city will only be listed once. UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL - 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 of a UNION are usually equal to the column names in the first SELECT statement in the UNION.