Oracle Set Operators

admin Oracle

Hello guys,

Hope you are doing well.
Today, we will talk about Oracle Operators.

An operator manipulates individual data items (operands or arguments) and returns a result.
An operator appears before or after an operand or between two operands. There are six types of nonlogical (non-Boolean) operators.

  1. Set Operators
  2. Arithmetic Operators
  3. Concatenation Operator
  4. Hierarchical Query Operators
  5. Multiset Operators
  6. User-Defined Operators

I will try to explain all these types of Operators and as these Operators are so descriptive there will be a separate blog on each topic.
So, Let’s discuss Set Operators in this blog.

Set Operators::

Set operators combine the results produced by two or more queries into a single result. Queries bound with Set operators are called Compound Queries.
If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

There are 4 types of Set operators. Let’s discuss these one by one with examples.

1. UNION

UNION operator checks output from all the compound queries and produces distinct output. Means if two queries are giving exact same rows then UNION shows those rows only once in the output.

If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

 
SELECT * FROM student01;

Output…
FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
Lex        De Haan             3
Alexander  Hunold              5
Bruce      Ernst               5
David      Austin              2
Valli      Pataballa           4

//

SELECT * FROM student02;


Output…


FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
Alexander  Hunold              5
Bruce      Ernst               5
Jason      Mallin              3

You can see in the above snippet that there are 2 records in both the tables which are exactly same. As per the properties of UNION operator when we combine these two tables using UNION, we should have combined output from both the tables and those 2 records which are exactly same should appear only once. Let’s combine these tables.

 
SELECT * FROM student01
UNION
SELECT * FROM student02;


Output…

FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
Alexander  Hunold              5
Bruce      Ernst               5
David      Austin              2
Jason      Mallin              3
Lex        De Haan             3
Valli      Pataballa           4

6 rows selected.

You can see clearly in the above output that those 2 records are showing only once and a new record has been added to the output from student02 table.

2. UNION ALL

UNION ALL operator does the same works as UNION but it shows all the records from the 1st query and all the records from the 2nd query and so on. It does check if there are duplicate records.
Let’s make a little change in above compound query and see the results.

 
SELECT * FROM student01
UNION ALL
SELECT * FROM student02;


Output…


FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
Lex        De Haan             3
Alexander  Hunold              5
Bruce      Ernst               5
David      Austin              2
Valli      Pataballa           4
Alexander  Hunold              5
Bruce      Ernst               5
Jason      Mallin              3

8 rows selected.

Look at the output. There are 8 rows in the output. It means we have all the records from the student01 table and all the records from student02 table even though there are 2 duplicate records.
This is the only difference between UNION and UNION ALL.

3. MINUS

After UNION and UNION ALL, let’s shake hands with 3rd set operator which is MINUS operator.
In Oracle database, it works exactly same as its name.
It means, it shows only those records from the 1st query which are not available in 2nd query.
It eliminates all the records from the 2nd query.

Let’s tweak the same compound query and see the results with MINUS operator.

 
SELECT * FROM student01
MINUS
SELECT * FROM student02;


Output…

FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
David      Austin              2
Lex        De Haan             3
Valli      Pataballa           4

3 rows selected.

This time only 3 records are showing in the output panel and as we have very small tables we can dive into the tables and cross check that these 3 records are from 1st query only. There is not a single record from the 2nd query.

Let’s head over to our next SET Operator which INTERSECT

4. INTERSECT

It defines it’s work itself. INTERSECT combines two or more queries and generates an output from all the queries. It checks for the rows in all the queries which are exactly same and eliminates the other rows from the result output.

An example using the same queries will clarify everything but this we will combine them with INTERSECT operator.

 
SELECT * FROM student01
INTERSECT
SELECT * FROM student02;


Output…

FIRST_NAME LAST_NAME       CLASS
---------- ---------- ----------
Alexander  Hunold              5
Bruce      Ernst               5

2 rows selected.

In the output section, we have only 2 rows. These are the only rows which are exactly the same in the output from both the queries.
You can dive into the queries and check for the common rows. If you have any questions related to these SET operators, please comment below. I will answer all your questions.

While combining the queries using any of the set operators, we should keep a few things in mind.
Some of words used below are/may be new to you but we will discuss them in detail in upcoming blogs.

  • There should be same number of columns selected in each query.
  • Column Name should be same in each query.
  • If column names are not same then we can use COLUMN ALIAS to match the column names. Just add the Original column name in Select statement and just after the column name add “as {desired column name}”.
  • The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
  • The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
  • You cannot also specify the for_update_clause with the set operators.
  • You cannot specify the order_by_clause in the subquery of these operators.
  • You cannot use these operators in SELECT statements containing TABLE collection expressions.

That’s it for SET operators. Hope you enjoyed the blog.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

Oracle-Datafile

Hello guys, Hope you are doing fantastic being an Oracle professional. I wrote a blog on Oracle TABLESPACE. If you […]

Oracle Virtual Column

Hello guys, Hope you are doing well. Today, we will talk about the Oracle Virtual Column. I will try to […]

Oracle-Tablespace

Hello guys, Hope you are doing well. Today, we will talk about Oracle Tablespace and I will try to cover […]

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *