Oracle UNION

Summary: in this tutorial, you will learn how to use the Oracle UNION operator to combine result sets returned by two or more queries.

Introduction to Oracle UNION operator

The UNION operator is a set operator that combines result sets of two or more SELECT statements into a single result set.

The following illustrates the syntax of the UNION operator that combines the result sets of two queries:

SELECT column_list_1 FROM T1 UNION SELECT column_list_1 FROM T2;
Code language: SQL (Structured Query Language) (sql)

In this statement, the column_list_1 and column_list_2 must have the same number of columns presented in the same order. In addition, the data type of the corresponding column must be in the same data type group such as number or character.

By default, the UNION operator returns the unique rows from both result sets. If you want to retain the duplicate rows, you explicitly use UNION ALL as follows:

SELECT column_list FROM T1 UNION ALL SELECT column_list FROM T2;
Code language: SQL (Structured Query Language) (sql)

Oracle UNION illustration

Suppose, we have two tables T1 and T2:

  • T1 has three rows 1, 2 and 3
  • T2 also has three rows 2, 3 and 4

The following picture illustrates the UNION of T1 and T2 tables:

Oracle UNION

The UNION removed the duplicate rows 2 and 3

The following picture illustrates the result of the UNION ALL of the T1 and T2 tables:

Oracle UNION ALL

As you can see, the UNION ALL retains the duplicate rows 2 and 3.

Oracle UNION examples

See the following employees and contacts tables in the sample database.

employees table
contacts table

A) Oracle UNION example

Suppose, you have to send out emails to the email addresses from both  employees and contacts tables. To accomplish this, first, you need to compose a list of email addresses of employees and contacts. And then send out the emails to the list.

The following statement uses the UNION operator to build a list of contacts from the employees and contacts tables:

SELECT first_name, last_name, email, 'contact' FROM contacts UNION SELECT first_name, last_name, email, 'employee' FROM employees;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle UNION example

B) Oracle UNION and ORDER BY example

To sort the result set returned by the UNION operator, you add an ORDER BY clause to the last SELECT statement as shown below:

SELECT first_name || ' ' || last_name name, email, 'contact' FROM contacts UNION SELECT first_name || ' ' || last_name name, email, 'employee' FROM employees ORDER BY name DESC;
Code language: SQL (Structured Query Language) (sql)

In this example, we sorted the list by name concatenated from the first and last names.

The following picture illustrates the result:

Oracle UNION and ORDER BY example

C) Oracle UNION ALL example

The following statement returns the unique last names of employees and contacts:

SELECT last_name FROM employees UNION SELECT last_name FROM contacts ORDER BY last_name;
Code language: SQL (Structured Query Language) (sql)

The query returned 357 unique last names.

Oracle UNION last_name example

However, if you use UNION ALL instead of UNION in the query as follows:

SELECT last_name FROM employees UNION ALL SELECT last_name FROM contacts ORDER BY last_name;
Code language: SQL (Structured Query Language) (sql)

The query returns 426 rows. In addition, some rows are duplicate e.g., Atkinson, Barnett. This is because the UNION ALL operator does not remove duplicate rows.

Oracle UNION ALL example

Oracle UNION vs. JOIN

A UNION places a result set on top another, meaning that it appends result sets vertically. However, a join such as INNER JOIN or LEFT JOIN combines result sets horizontally.

The following picture illustrates the difference between union and join:

Oracle UNION vs INNER JOIN

In this tutorial, you have learned how to use the Oracle UNION operator to combine result sets of multiple queries.

Was this tutorial helpful?