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

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
Previous article: Oracle Operators: Part 1
Next article: Oracle Operators: Part 3


Similar Articles