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

Table Employees2

Query

SELECT Designation

FROM Employees1;

UNION

SELECT Designation

FROM Employees2;

Output

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