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:

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:

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.

A) Oracle UNION example

Suppose, you have to send out emails to the email addresses of 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:

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:

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.

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 duplicates e.g., Atkinson, Barnett. This is because the UNION ALL operator does not remove duplicate rows.

Oracle UNION vs. JOIN

A UNION places a result set on top of 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:

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