SQL SET Operations

SQL SET Operations enables to combine the results from two or more SELECT statements.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.All set operators have equal precedence.If a SQL statement contains multiple set operators,Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

Types of set operations:

• UNION ALL
• UNION
• MINUS
• INTERSECT

UNION ALL
Combines the results of two SELECT statements into one result set.

EX:select * from sales2000 UNION ALL select * from sales2001 ;

UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

EX:select * from sales2000 UNION select * from sales2001;

MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

EX:select * from sales2000 MINUS select * from sales2001;

INTERSECT
Returns only those rows that are returned by each of two SELECT statements

EX:select * from sales2000 INTERSECT select * FROM sales2001;