UNION Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

UNION Operator in Oracle with Examples

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

UNION Operator in Oracle

The UNION operator is used to combine the result set of two or more SELECT statements into a single result set and then eliminates any duplicate rows from the final result set. That means the UNION Operator selects only the distinct values.

Following is the Syntax to use UNION Operator in MySQL.

Examples to understand UNION Operator in Oracle:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the UNION 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');

Combining the rows of EmployeeUk and EmployeeUSA using UNION Operator in Oracle

Let us combine the two tables’ data using UNION Operator and see the result. In our example, both the EmployeeUK and EmployeeUSA tables have seven records.

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

The above statement combines the results of two queries with the Oracle UNION SET operator, which eliminates duplicate selected rows. Once you execute the above query, you will get the following result set. Please observe, here we don’t have any duplicate data. Here, in the result set, we got a total of 11 rows out of 14 rows. This is because 3 rows are present in both the result set.

UNION with ORDER BY Clause in Oracle:

For UNION and 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 or 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
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 is not properly ended.

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

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

Difference between JOIN and UNION in Oracle

JOINS and UNIONS are two different things. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries, whereas JOINS, retrieves data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, whereas JOINS combines columns from 2 or more tables. If this is not clear at the moment then don’t worry, we will discuss this with examples when we discuss JOINs in Oracle.

In the next article, I am going to discuss UNION ALL Operator in Oracle. Here, in this article, I try to explain UNION Operator in Oracle with Examples and I hope you enjoy this UNION Operator in Oracle with Examples article. If you have any queries regarding the Oracle UNION Operator, then please let us know by putting your query in the comment section.