Set Operator uses and Benefits in Oracle Database #DOTC_Mdb

 

              Set Operator uses and Benefits in Oracle Database 

Today we will discuss about different Set operators used in SQL with examples. SET operator used to select data from multiple tables as Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

Through SET Operator in SQL we don’t need to use Joins and make alias of table to get the common record and all distinct row selected by query.

SQL Set Operators are of 4 parts which work similar to Mathematics which we learn in class 10^th or so.

1.     UNION

2.     UNION ALL

3.     INTERSECT

4.     MINUS/EXCEPT.

   

Things to Remember while using SET operator:

Select clause should have the same number of columns and columns must be of same data type, Column needs to be specified in the same order in the select clause.

 

UNION: UNION set operator use to select all distinct rows selected by either query. UNION returns all the unique rows returned from both the queries. UNION set operator mainly used to combine the both select statement result set and removes duplicate records between the tables. So it returns distinct record.

Using tables we can use UNION set operator to see the result.

To see the distinct record from two tables we use below query to get the data .

Select location from employee

UNION

Select location from department;

Output of the above query is as below:

 

 

Here you can see 7 distinct records from both the table as Chennai was in Employee table and Ranchi was in Department table but we get all the distinct records no duplicates. You can also select 2 or more column with same data type to retrieve the data.

 

UNION ALL : UNION ALL : UNION ALL set operators  mainly use to select all rows selected by either query, including all duplicates. In other way we can say UNION ALL combines the both SELECT statement result sets and return  as it is. So it can contain duplicate depends on the data from the tables.

 

For Example we have location_name common in both table employee and department . To see the records from  both the tables by using UNION ALL we use below query to get the data.

Select Location from Employee

UNION ALL

Select Location from Department;

Output of the above query is as below :

 

 

Here we can see all the records from both the table , Also if we have multiple duplicate records in column UNION ALL will display all that records as well for example if Patna comes 2 times in Employee table and 1 times in Department table then it will give 3 Patna as output.You can also select 2 or more column with same data type to retrieve the data.

Difference between Union and Union ALL is UNION returns the distinct rows whereas Union all returns all the rows. So we need to be careful while choosing these set operators.

 

INTERSECT: INTERSECT operator used to get all distinct rows selected by both queries. INTERSECT mainly used to combines the both SELECT statement data-sets and return  the distinct common rows between the statements. So If a record exists in one query and not in the other, it will be omitted by using INTERSECT set operator.

 

For Example we have location_name common in both table employee and department . To see the records from  both the tables by using INTERSECT we use below query to get the data.

 

Select Location from employee

INTERSECT

Select location from department;

 

 

Here you are able to see the match records from both the table , not any unmatched records from both the tables . INTERSECT operator is used to retrieve the common records from both the select clause of the Intersect Operator while we have seen in UNION and UNION all it gives all the records from both the tables.You can also select 2 or more column with same data type to retrieve the data.

 

MINUS : MINUS Set Oprator get  all distinct rows selected by the first query but not the second. In other word It combines  both SELECT statement and return  the  rows which is selected from first select statement and then remove from the data-set all the rows retrieved from second select statement.

 

 

For Example we have location_name common in both table employee and department . To see the records from  both the tables by using MINUS we use below query to get the data.

 

Select Location from employee

MINUS

Select  location from department;

 

 

 

Here we see only 1 record as this records not match in department table so we get only 1 record.You can also select 2 or more column with same data type to retrieve the data.

MINUS operator is not supported in  RDBMS like MYSQL and SQL server.

This is all about set operators in SQL and I hope you are able to understand all the set Operators please try with different columns and table to see the result and output. 

Please go through this blog and let me know if you have any doubts.

Thanks