The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT
statements.
Notice that each SELECT statement within the UNION must have the same number
of columns. The columns must also have similar data types. Also, the columns in
each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To
allow duplicate values, use the ALL keyword with UNION.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
PS: 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.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
| CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
| 2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
| 3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
And a selection from the "Suppliers" table:
| SupplierID |
SupplierName |
ContactName |
Address |
City |
PostalCode |
Country |
| 1 |
Exotic Liquid |
Charlotte Cooper |
49 Gilbert St. |
Londona |
EC1 4SD |
UK |
| 2 |
New Orleans Cajun Delights |
Shelley Burke |
P.O. Box 78934 |
New Orleans |
70117 |
USA |
| 3 |
Grandma Kelly's Homestead |
Regina Murphy |
707 Oxford Rd. |
Ann Arbor |
48104 |
USA |
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;
| City |
|---|
| Aachen |
| Albuquerque |
| Anchorage |
| Ann Arbor |
| Annecy |
| Århus |
| Barcelona |
