SQL UNION is used to combine results of two or more SQL SELECT queries into one. The syntax of SQL UNION is as follows:
SELECT column_list1 FROM table1
UNION (DISTINCT | ALL)
SELECT column_list2 FROM table2
Basically the first and the second query can be any SQL SELECT queries with a restriction that the column_list1 and column_list2 have to be compatible. It means both columns lists must have the same number of columns, and each corresponding column must has the same data type or at least convertible data type.
By default SQL UNION eliminates all duplicate records, in this case NULL values are considered as a single value. To enable duplication of records, you can use ALL keyword followed after UNION explicitly. Be noted that by default the DISTINCT is used if you don’t specify anything after UNION.
Let’s take a look at several examples of using SQL UNION. Suppose you want to find all the cities of all customers and suppliers. In this case, you can use SQL UNION to combine cities of both customers and suppliers as follows:
SELECT city FROM customers
UNION
SELECT city FROM suppliers
Here is the excerpt output:
city
---------------
Aachen
Albuquerque
Anchorage
Barcelona
Barquisimeto
Bergamo
Berlin
Bern
Boise
Brandenburg
Bruxelles
If you use UNION ALL you will see duplicate values in the output by performing the following query:
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
In this tutorial, you've learned how to use SQL UNION to combine result of two or more SQL SELECT queries into one.