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.

Use of UNION command

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

 EMPLOYEE 1

EMPLOYEE 2 

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

SELECT emp_name FROM Employee1
UNION 
SELECT emp_name FROM Employee2

Result :-       

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. 

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 :-       

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.

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 :-    

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.

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 :-   

​SELECT emp_name FROM Employee2
MINUS
SELECT emp_name FROM Employee1

Result :-