Set Operators in Oracle

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS.
 All set operators have equal precedence.


UNION:
------

The following statement combines the results of two queries with the UNION operator,
 which eliminates duplicate selected rows. This statement shows that you must match datatype
(using the TO_CHAR function) when columns do not exist in one or the other table:

SQL> SELECT location_id, department_name "Department",
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name
   FROM warehouses;


LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction


UNION ALL:
---------

The UNION operator returns only distinct rows that appear in either result,
 while the UNION ALL operator returns all rows.
The UNION ALL operator does not eliminate duplicate selected rows:

SQL> SELECT product_id FROM order_items
      UNION
     SELECT product_id FROM inventories;

SQL> SELECT location_id  FROM locations
     UNION ALL
     SELECT location_id  FROM departments;

A location_id value that appears multiple times in either or both queries (such as '1700')
is returned only once by the UNION operator, but multiple times by the UNION ALL operator.


INTERSECT:
----------

The following statement combines the results with the INTERSECT operator,
 which returns only those rows returned by both queries:

SQL> SELECT product_id FROM inventories
      INTERSECT
     SELECT product_id FROM order_items;

MINUS :
-------

The following statement combines results with the MINUS operator,
 which returns only rows returned by the first query but not by the second:

SQL> SELECT product_id FROM inventories
     MINUS
     SELECT product_id FROM order_items;



If you want to use ORDER BY in a query involving set operations,
you must place the ORDER BY at the end of the entire statement.
The ORDER BY clause can appear only once at the end of the compound query.
The component queries can't have individual ORDER BY clauses. For example:

SQl> SELECT cust_nbr, name FROM customer WHERE region_id = 5
     UNION
     SELECT emp_id, lname FROM employee WHERE lname = 'MARTIN'
     ORDER BY cust_nbr;


Restrictions on the Set Operators:
---------------------------------
-->Columns and datatype in  the select queries should match.
   (i.e number of columns in the first query equal to number of columns in the second query)

--->The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

--->The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.

--->If the select list preceding the set operator contains an expression,
    then you must provide a column alias for the expression in order to refer to it
    in the order_by_clause.

--->we cannot also specify the for_update_clause with the set operators.

--->we cannot specify the order_by_clause in the subquery of these operators.

--->we cannot use these operators in SELECT statements containing TABLE collection expressions

Ur's

AmarAlam