In continuation of my previous article Oracle Operators (Part 1) let’s start with Set Operators.
5. Set Operator
In Oracle, to join the result of two or more select statements we use the set operator. The queries that contain set operators are known as Compound Queries. Set Operators include:
- UNION
- UNION ALL
- INTERSECT
- MINUS.
a) UNION Operator
In case there are multiple SELECT queries and they all are joined using a UNION operator, Oracle shows the result after removing all the duplicate queries in ascending order (by default) without ignoring the NULL value. It is one of the most widely used SET operators.
Diagram
![Union Operator]()
Note: The UNION operator returns results from both queries after eliminating duplicates.
Syntax
SELECT <Column_Name>
FROM Table1;
UNION
SELECT <Column_Name>
FROM Table2;
UNION
SELECT <Column_Name>
FROM Table3;
Example
Note: Assume the following two Employees tables to understand the examples.
Table Employees1
| Emp_Id |
Emp_Name |
Designation |
| 1001 |
ABC |
Tech. Lead |
| 1002 |
ASD |
Software Developer |
| 1003 |
QWE |
Software Developer |
| 1004 |
RST |
Project Manager |
| 1005 |
PQR |
Tech. Lead |
| 1006 |
BCD |
Director |
| 1007 |
LMN |
Receptionist |
Table Employees2
| Emp_Id |
Emp_Name |
Designation |
| 1111 |
AAA |
Software Developer |
| 1112 |
RRR |
Project Manager |
| 1113 |
BBB |
Sr.Software Developer |
| 1114 |
HHH |
Tech. Lead |
| 1115 |
GGG |
HR |
Query
SELECT Designation
FROM Employees1;
UNION
SELECT Designation
FROM Employees2;
Output
| Designation |
| Tech. Lead |
| Software Developer |
| Project Manager |
| Sr.Software Developer |
| HR |
| Director |
| Receptionist |
b) UNION ALL Operator
The UNION ALL operator is very much similar to the UNION operator but the only difference is that, the UNION ALL operator merges the result sets of two or more queries. Here the output does not remove the duplicate records and the sorting of the data items.
Diagram
![UNION ALL Operator]()
Note: The UNION ALL operator returns results from both queries, including all duplicates.
Syntax
SELECT <Column_Name>
FROM Table1;
UNION ALL
SELECT <Column_Name>
FROM Table2;
UNION ALL
SELECT <Column_Name>
FROM Table3;
Example
Using the preceding two Employees tables.
Query
SELECT Designation
FROM Employees1;
UNION ALL
SELECT Designation
FROM Employees2;
Output
| Designation |
| Tech. Lead |
| Software Developer |
| Software Developer |
| Project Manager |
| Tech. Lead |
| Director |
| Receptionist |
| Software Developer |
| Project Manager |
| Sr.Software Developer |
| Tech. Lead |
| HR |
c) INTERSECT Operator
The Intersect operator returns the only rows present in both tables. It returns the unique rows, also it is not at all important which query is first and which is second.
Diagram
![INTERSECT Operator]()
Note: The INTERSECT operator returns rows that are common to both queries.
Syntax
SELECT <Column_Name>
FROM Table1;
INTERSECT
SELECT <Column_Name>
FROM Table2;
INTERSECT
SELECT <Column_Name>
FROM Table3;
Example
Using the above two Employees tables.
Query
SELECT Designation
FROM Employees1;
INTERSECT
SELECT Designation
FROM Employees2;
Output
| Designation |
| Tech. Lead |
| Software Developer |
| Project Manager |
d) MINUS Operator
This operator is also known as EXCEPT; it joins the results of the two nested tables and merge them into one. Which means that it returns the difference between the first and second SELECT statement.
Diagram
![MINUS Operator]()
Note: The MINUS operator returns rows in the first query that are not present in the second query.
Syntax
SELECT <Column_Name>
FROM Table1;
MINUS
SELECT <Column_Name>
FROM Table2;
MINUS
SELECT <Column_Name>
FROM Table3;
Example
Using the above two Employees tables.
Query
SELECT Emp_name, Designation
FROM Employees1;
MINUS
SELECT Emp_name, Designation
FROM Employees2;
Output
| Emp_Name |
Designation |
| BCD |
Director |
| LMN |
Receptionist |