Uses of Set Operators

Set Operations

Set operations is used to merge query results by combining two or more queries into a particular single result. 
SQL support three set operators which have the proper pattern. 
<query 1> <set operators> <query 2>

Union 

The UNION command is used to select similar information from the two table, same as like the JOIN command. It discard the identical data from the result. Whenever we are using the UNION command all the selsected column need to be a same data type.In UNION we can select only different  values.

SQL  Statement 1
UNION 
SQL  Statement 2

Use of UNION command

Example :-We have a two table that is EMPLOYEE 1 and EMPLOYEE 2

 EMPLOYEE 1

emp_no
emp_name
121Karan 
122Shiva
123Sam

 

EMPLOYEE 2 

emp_no
emp_name
167Ravi
168John
169Sam

List all the different employee name of company abc and company xyz:-

SELECT emp_name FROM Employee1
UNION 
SELECT emp_name FROM Employee2

Result :-       

emp_name
Karan
Shiva
Sam
Ravi
John

The UNION command cannot used all the employees from EMPLOYEE 1 and EMPLOYEE 2. In the above table we have two employees with the equal name and only one of them are listed in the table because UNION command select only a different values. 

Union all

The UNION ALL command work same as UNION command instead of that it select all the value by using UNION ALL. 

SQL  Statement 1
UNION ALL
SQL  Statement 2

Use of UNION ALL command

Example :- List all the employee name of company abc and company xyz:-

​SELECT emp_name FROM Employee1
UNION ALL
SELECT emp_name FROM Employee2

Result :-       

emp_name
Karan
Shiva
Sam
Ravi
John
Sam

 

Intersect

Intersect work same as UNION command.  Both of them work on a two SQL statement. The difference is that UNION basically behave as a  OR operator  but the INTERSECT command behave as a  AND operator.
INTERSECT command will only return different value.

SQL  Statement 1
INTERSECT
SQL  Statement 2

Example :-  Above we have a two table that is EMPLOYEE 1 and EMPLOYEE 2
we have to find out the 

​SELECT emp_name FROM Employee1
INTERSECT
SELECT emp_name FROM Employee2

Result :-    

emp_name
Karan
Shiva

 

Minus

The MINUS operates on two SQL statements. It work slightly different from the union uninon all and intersect. It consume all the result from the first SQL statement, and then deduct out the ones that are existing in the second SQL statement to get the final answer. If the second SQL statement add result not exist in the first SQL statement, such results are ignored.

SQL  Statement 1
MINUS
SQL  Statement 2

Example :-  Above we have a two table that is EMPLOYEE 1 and EMPLOYEE 2
We want to find out comprehend tuples that are present in  EMPLOYEE 1  but not in  EMPLOYEE 2 

Build all tuples that are present in  EMPLOYEE 1 but not in  EMPLOYEE 2

​SELECT emp_name FROM Employee1
MINUS
SELECT emp_name FROM Employee2

Result :-   

emp_name
Karan
Shiva

 

​SELECT emp_name FROM Employee2
MINUS
SELECT emp_name FROM Employee1

Result :-  

emp_name
Ravi
John

Keywords: