Oracle Operators: Part 2

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



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



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



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



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