Summary: in this tutorial, you will learn how to use SQL UNION to combine result sets of two or more SQL queries.
SQL UNION Syntax
SQL UNION combines result sets of two or more SQL SELECT queries into a single result set that contains all rows in all queries.
The syntax of the SQL UNION statement is as follows:
SELECT column_list1 FROM table1 UNION (DISTINCT | ALL) SELECT column_list2 FROM table2
You put the UNION operator between two SQL SELECT statements. The following are the rules that you must follow to combine multiple result sets of queries:
- The order and number of the columns in all queries must be the same.
- The data type of the corresponding columns must be compatible, or at least convertible.
By default, the SQL UNION eliminates duplicate rows. The SQL UNION operator treats all NULL values as a single NULL value when evaluating duplicate. When combine with UNION, the ALL or DISTINCT operator is optional.
- The ALL operator allows duplicate rows to appear in the combined result set.
- The DISTINCT operator eliminates duplicate rows. If you don’t explicitly use any operator, the DISTINCT operator is used by default.
Let’s take a look at some examples of using SQL UNION statement.
SQL UNION Examples
To find cities of both customers and suppliers, first we can query city of customer from customers table and city of suppliers from supplies table. Then, we combine two result sets into one using SQL UNION as follows:
SELECT city FROM customers UNION SELECT city FROM suppliers
SQL UNION ALL example
If we use the UNION ALL, we will see duplicate cities in the output as following query:
SELECT city FROM customers UNION ALL SELECT city FROM suppliers
SQL UNION with ORDER BY clause example
To sort the combined result set, we use the ORDER BY clause. However, we have to put the ORDER BY clause in the last query. Notice that the ORDER BY clause is applied to the combined result set.
SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city
In this tutorial, we have shown you how to use SQL UNION to combine result sets of two or more queries into a single result set.