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 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;
In this statement, the
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;
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:
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.
See the following
contacts tables in the sample database.
Suppose, you have to send out emails to the email addresses from both
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
SELECT first_name, last_name, email, 'contact' FROM contacts UNION SELECT first_name, last_name, email, 'employee' FROM employees;
Here is the result:
ORDER BY example
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;
In this example, we sorted the list by name concatenated from the first and last names.
The following picture illustrates the result:
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;
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;
The query returns 426 rows. In addition, some rows are duplicate e.g.,
Barnett. This is because the
UNION ALL operator does not remove duplicate rows.
Oracle UNION vs. JOIN
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.