Ø 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;