SET OPERATORS

S E T O P E R A T O R S

Here the data retrieved by a select statement is treated as a set. Set operators are used to combine Two sets retrieved by two different select statements. The two select statements may retrieve data from the same table or from different tables. Set operators in SQL are as follows.

UNION
UNION ALL
INTERSECT
MINUS

1. UNION : The set operator Union combines the data retrieved by two different select statements by eliminating duplicate values that are obtained after combining the data of two select statements.
Following Example finds all jobs that are in departments 10, 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are duplicated. Union eliminates the duplicated values and final result of union is Manager, Clerk, Accountant, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION
Select JOB from EMP where Deptno = 20;

2. UNION ALL : Like UNION, UNION ALL also combines two sets that are retrieved by two different select statements except that it does not eliminate duplicate values.
If we take the same above example with union all, then the final result is Manager, Clerk, Accountant, Manager, Clerk, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION ALL
Select JOB from EMP where Deptno = 20;

3. INTERSECT : The set operator INTERSECT results the values that are common in two sets that are retrieved by two different select statements.
Following example finds the jobs that are present in both departments 10 and 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are Common in both sets. Hence the final result is Manager and Clerk.

Example : Select JOB from EMP where Deptno=10
INTESRSECT
Select JOB from EMP where Deptno = 20;

4. MINUS : The set operator MINUS retrieves the values that are present in first set retrieved by first select statement and not in second set retrieved by second select statement.
Following example finds the jobs that are in department 10, but not in department 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Here Accountant job is present in department 10 but not in department 20. Hence the final result is Accountant.

Example : Select JOB from EMP where Deptno=10 MINUS
Select JOB from EMP where Deptno = 20;

RULES FOR WORKING WITH SET OPERATORS :

Both Select statements must contain same number of columns in the selection list.
Corresponding columns in both the select statements must be of same type.
When using order by clause with set operators, order by clause must be written at the end of second select statement only.
In order by clause we have to specify the position of the column in selection list instead of column name.