SQL UNION

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 table2Code language: SQL (Structured Query Language) (sql)

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 combining 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 the customer from customers table and city of suppliers from supplies table. Then, we combine two result sets into one using the SQL UNION as follows:

SELECT city FROM customers
UNION
SELECT city FROM suppliersCode language: SQL (Structured Query Language) (sql)

SQL UNION ALL example

If we use the UNION ALL, we will see duplicate cities in the output as the following query:

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliersCode language: SQL (Structured Query Language) (sql)

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 cityCode language: SQL (Structured Query Language) (sql)

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.