In Oracle Database, the UNION operator allows us to combine the results from two queries into a single result set.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

TEACHERID	TEACHERNAME
1	Warren
2	Ben
3	Cathy
4	Cathy
5	Bill
6	Bill

STUDENTID	STUDENTNAME
1	Faye
2	Jet
3	Spike
4	Ein
5	Warren
6	Bill

Here’s an example of using the UNION operator to return the names of all teachers and students:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;

Result:

TEACHERNAME
Ben
Bill
Cathy
Ein
Faye
Jet
Spike
Warren

By default, the UNION operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default. So the above results contain just one each of Warren, Cathy and Bill. This is despite the fact that the combined tables actually contain two Warrens, two Cathys, and three Bills (there are two teachers called Cathy, a teacher and a customer called Warren, and two called Bill, as well as one student called Bill).

Include Duplicates

We can use the ALL keyword to include duplicate values in the results:

SELECT TeacherName FROM Teachers
UNION ALL
SELECT StudentName FROM Students;

Result:

TEACHERNAME
Warren
Ben
Cathy
Cathy
Bill
Bill
Faye
Jet
Spike
Ein
Warren
Bill

This time we got twelve rows instead of the eight that we got in our first example.

We can see that both Cathys were returned and all three Bills were returned.

Some Things to Remember

Note that the expressions must match in number and must be in the same data type group. Therefore, we can’t do the following:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;

Result:

ORA-01789: query block has incorrect number of result columns

Or this:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentId FROM Students;

Result:

ORA-01790: expression must have same datatype as corresponding expression

Although, we can use functions like TO_CHAR() to convert a column to a suitable data type group:

SELECT TeacherName FROM Teachers
UNION
SELECT TO_CHAR(StudentId) FROM Students;

Result:

TEACHERNAME
1
2
3
4
5
6
Ben
Bill
Cathy
Warren