WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL UNION Operator


The SQL UNION operator combines the result of two or more SELECT statements.


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 demo1
UNION
SELECT column_name(s) FROM demo2;

SQL UNION ALL Syntax

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

Database

In this tutorial we will use the well-known database.

Below is a selection from the "Customers" table:

CustomerID CustomerName UserName Address City PostalCode Country
1

Andre C. Holloway Thiletwed52 Giraffe Hill Drive Frisco TX 75034 Usa
2 Melissa Araujo Cavalcanti Wriand Pointe Lane Miami FL 33179 USA
3 Victor Goncalves Correia Antonio Moreno rue Grande Fusterie BRON 69500 France

And a selection from the "Distributor" table:

SupplierID DistributorName UserName Address City PostalCode Country
1 Guttormur Arnlaugsson Mithency Tværgyden Gsa 8765 Danmark
2 Haddur Sævarsson Arde1973 Windfall Grove Pinehill North Shore 0632 New zealand
3 Hildigunn Jensdóttir Regina Murphy Conaway Street Ferdinand IN 47532 USA

SQL UNION Example

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

Example

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

SQL UNION ALL Example

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

Example

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

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='USA'
UNION ALL
SELECT City, Country FROM Distributor
WHERE Country='USA'
ORDER BY City;