Set Operators

Defining Set Operators:

Set Operators combine the Result Set of two or more SELECT queries into a single result. The query containing the set operator is called Complex Query.

Key Facts about Set Operators :

1. Execution of the Query containing Set Operators always starts from Left to Right. Exception to this is parenthesis which are given more preference than operators and may define some other order explicitly.
2. ORDER BY Clause can be used only once at the end of the Complex query. Individual Query cannot use ORDER BY Clause.
3. FOR UPDATE Clause cannot be used with SET Operators.
4. The Column names of the Output of Compound queries containing SET Operators are defined by the Column Names of the first query.

Pre-Requisites for Set Operators :

For Set Operators to perform , Both the SELECT Queries should have :

1. Same Number of Columns
2. Same Data Types
3. Same Order of the Columns.

But what if the Number of Columns and Data Types are Different and still we want to perform SET Operation ??

This is done by Explicit Matching . In such scenario , NULL is inserted at the missing position in order to make the number of columns and datatype match . For numeric datatype , zero can  also be inserted in place of NULL.

Output Datatype of the Result set is dependent upon Data Types and Length of Input Query 1 & 2 as follows :

But Why the Output Datatype is Varchar2 even when both the inputs have Char datatypes of variable length. ??

Suppose for column NAME ,Query 1 has CHAR Datatype defined of length 15 and Query 2 has CHAR Datatype defined of length 10. The first record in the output that came is ‘SHIKHA’ . As we studied in earlier post , CHAR has a fixed length property e.g. length 15 it will occupy memory for 15 characters , although name ‘SHIKHA’ holds only 6 Characters , which means memory of 9 characters is wasted. Compared to this , Varchar2 will only occupy memory for 6 characters and will release the remaining memory.

This is the logic behind why Oracle defines output datatype as VARCHAR2 .

Types Of Set Operators:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION :

UNION Operator combines the Result Set of two or more SELECT Queries into a single result and returns all the rows from both the SELECT Queries ( i.e both the tables) in a sorted order excluding Duplicates.

How UNION Works :

Step 1 : UNION Operator first combines the output of both the SELECT Queries.

Step 2 : It then performs a Sort Operation and arrange the Result Set in ascending order.

Step 3 : It then removes the duplicates ,fetches Unique values and gives the final Result.

UNION ALL :

UNION ALL Operator combines the Result Set of two or more SELECT Queries into a single result and returns all the rows from both the SELECT Queries ( i.e both the tables) without sorting including Duplicates.

Difference Between UNION and UNION ALL :

UNION combines rows from both tables excluding duplicates whereas UNION ALL combines rows from both the tables including duplicates.

UNION is slower as it performs a Sort Operation and eliminates duplicates whereas UNION ALL is faster as it does not performs any Sort Operation neither eliminates duplicates.

INTERSECT :

INTERSECT Operator combines the Result Set of two or more SELECT Queries into a single result and returns only the common rows from both the SELECT Queries ( i.e both the tables) . The Output does not contains any duplicates and is Sorted in an ascending order.

MINUS :

MINUS Operator returns all the rows from output of first SELECT Query which are not there in the output of the second SELECT Query.The Output does not contains any duplicates and is Sorted in an ascending order.

Difference Between INTERSECT and MINUS :

INTERSECT displays the common rows of both the Result Set whereas MINUS eliminates the common rows of both the Result Set and displays uncommon rows of first Query.