Set Operators

Bismillahir Rahmanir Raheem
There are four set operators union,union all,intersect,minus .
Set operators combine two or more separate select statement so that their o/p is merged in some way.

Set operators are ideal for a variety of situations where a select statement`s o/p can be combined with
other data that isn`t necessarily related through a structured key relationship.

Union-combines row sets.eliminate duplicate row sets
Union Alll-combines row sets.does not eliminate duplicate row sets
intersect-includes only those row sets present in both queries
minus-subtracts the rows of the second row set from the rows in the first row set.

Rules that ensure select statements will combine successfully with any of the set operators
1.Number of expressions selected in select lists must be identical in each select statement
2.Datatypes of each expression must share the same datatype group.It means datatypes that are identical or can be made
  identical through automatic datatype conversion
3.Clob and Blob datatypes can not be used
4.Order by clause can not be included in select statement except for the final select statement and only once.

Note---
Select statements are not required to have any sort of primary key/foreign key relationships.

Union--

Union All--

Intersect--

intersect operator

Minus--

Minus Operator

Notice the column heading of the o/p ,column heading is same as columns selected in the first select statement.

Control the order of Rows returned----
Their are two ways to sort rows of o/p resulting from a series of select statement combined with set operators
1.Order By-by position  
2.Order By-by reference

Note--You can not use column names of the second select statement while sorting because column names of first select statement are in force.

by position--select id,name
from k1
union
select class,section
from k2
order by name;
------Or you could have written it as
select id,name
from k1
union
select class,section
from k2
order by 2;

You will get the same answer.

by reference--
Order by reference is when you name one of the columns in the select statement`s expression list.