UNION ALL Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

UNION ALL Operator in Oracle with Examples

In this article, I am going to discuss UNION ALL Operator in Oracle with Examples. Please read our previous article, where we discussed UNION Operator in Oracle with Examples. At the end of this article, you will understand UNION ALL Operator in detail with Examples.

UNION ALL Operator in Oracle

The UNION ALL operator is used to combine the result set of two or more SELECT statements into a single result including the duplicate values. Following is the pictorial representation of UNION ALL Operator.

Following is the Syntax to use UNION ALL Operator in Oracle.

Examples to understand UNION ALL Operator in Oracle:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the UNION ALL Operator in Oracle with examples.

Please use the below SQL Script to create the EmployeeUK and EmployeeUSA tables with the required data.

SET linesize 300;
DROP Table EmployeeUK;
DROP Table EmployeeUSA;

CREATE TABLE EmployeeUK
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUK VALUES(1, 'Pranaya', 'Rout', 'Male','IT');
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUK VALUES(3, 'Preety', 'Tiwary', 'Female','HR');
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUK VALUES(5, 'Anurag', 'Mohanty', 'Male','IT');
INSERT INTO EmployeeUK VALUES(6, 'Rajesh', 'Pradhan', 'Male','HR');
INSERT INTO EmployeeUK VALUES(7, 'Hina', 'Sharma', 'Female','IT');

CREATE TABLE EmployeeUSA
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUSA VALUES(1, 'James', 'Pattrick', 'Male','IT');
INSERT INTO EmployeeUSA VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUSA VALUES(3, 'Sara', 'Taylor', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(5, 'Sushanta', 'Jena', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(6, 'Mahesh', 'Sindhey', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(7, 'Hina', 'Sharma', 'Female','IT');

UNION ALL Operator Example in Oracle:

The following query combines the result sets of two select statements into a single result set using the UNION ALL operator in Oracle.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION ALL
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

Once you execute the above UNION ALL query, you will get the following result set. Please observe, here we got all the 14 rows in the result set.

UNION ALL with ORDER BY Clause in Oracle:

For UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be the same. If you want to sort, the results of UNION ALL, the ORDER BY clause should be used on the last SELECT statement as shown in the below query.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION ALL
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA
ORDER BY FirstName;

Now when you execute the above query and you will get the following output. Here you can see the employees are sorted according to their FirstName column values.

ORDER BY Clause in the First select statement:

If we use the ORDER BY Clause in the first select statement as shown in the following query, it will raise a syntax error i.e. SQL command not properly ended.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK ORDER BY FirstName
UNION ALL
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

Now when you execute the above query and you will get the following error.

Differences between UNION and UNION ALL Operator in Oracle

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows. When we use a UNION operator, in order to remove the duplicate rows from the result set, it has to do a distinct operation which is time-consuming. For this reason, UNION ALL is much faster than UNION Operator in Oracle. 

In the next article, I am going to discuss INTERSECT Operator in Oracle with Examples. Here, in this article, I try to explain UNION ALL Operator in Oracle with Examples and I hope you enjoy this article.