Set Operators

Ø  These Operators are used to combine information of similar data type from one or more than one table.
Ø  Data type of the corresponding columns in all the select statements should be same.
Ø  The different types of SET Operators are…
o   UNION
o   UNION ALL
o   INTERSECT
o   MINUS
Ø  SET Operators can combine two or More Queries into one result.
Ø  The result of each select statement can be treated as a set, and SQL set operations can be applied on those sets to arrive at a final result.
Ø  SQL Statements containing SET Operators are referred to as compound queries, and each select statement in a compound query is referred to as a component query.
Ø  Set operators are often called vertical joins, as the result combines data from two or more selects based on columns instead of rows.
The Generic Syntax:
<Component query>
{UNION | UNION ALL |MINUS | INTERSECT}
<Component query>;
UNION:
Combines the result of two select statements into one result set, and then eliminates any duplicate rows from that result set.
UNION ALL:
Combines the result of two select statements into one result set including duplicates.
INTERSECT:
Returns only those rows that are returned by each of two select statements.
MINUS:
Takes the result set of one select statement, and removes those rows that are also returned by a second select statement.
Point of Concentration:
Ø  The Queries are all executed independently but their output is merged.
Ø  Only Final query ends with a semicolon.
Rules and Restrictions:
Ø  The result sets of both the queries must have the same number of columns.
Ø  The data type of each column in the second result set must match the data type of its corresponding column in the first result set.
Ø  The two select statements may not contain an order by clause, the final result of the entire set operations can be ordered.
Ø  The columns used for ordering must be defined through the column number.
Illustrations:
  • Select Empno, Ename From Emp where Deptno = 10 UNION Select Empno, Ename From Emp where deptno = 30 Order by 1; 
  • Select Empno, Ename, Job From Emp where Deptno = (Select Deptno From Dept where Dname = ’SALES’ ) UNION Select Empno, Ename, Job From Emp Where Deptno = (select Deptno From Dept Where Dname = ’ACCOUNTING’ ) Order by 1;
  •  Select Empno, Ename From Emp where Deptno = 10 UNION ALL Select Empno, Ename From Emp where Deptno = 30 Order by 1;
  • Select Empno, Ename From Emp where Deptno =10 INTERSECT Select Empno, Ename From Emp where Deptno = 30 Order by 1;
  • Select Empno, Ename From Emp where Deptno = 10 MINUS Select Empno, Ename From Emp where Deptno = 30 order by 1;
  • Select Job From Emp where Deptno = 20 UNION Select Job From Emp where deptno  = 30;
  • Select Job From Emp where Deptno = 20 UNION ALL select Job From Emp where deptno = 30;
  • Select Job From Emp where Deptno = 20 INTERSECT select Job From Emp where deptno = 30;
  • Select Job From Emp where Deptno = 20 MINUS select Job From Emp where deptno = 10;
  • Select ROWNUM, Ename From Emp where ROWNUM < 7 MINUS Select ROWNUM, Ename From Emp where ROWNUM < 6;