Lesson 24 : UNION,INTERSECT and MINUS

SET OPERATORS

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

UNION

UNION query is to combine the result sets of two or more SELECT queries. It eliminates duplicate rows between those SELECT query result sets.
Note : Each SELECT quries within the UNION query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> UNION SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 UNION SELECT * FROM emp where dept=20;

SQL> select * from hr.emp UNION select * from sales.emp;

Here, it combines emp tables from hr and sales schemas. Note that, need adequate privileages to
select records from other schemas.

Example to cobines 3 select queries.

SQL> select name from hr.emp UNION select name from sales.emp UNION select name from purchase.emp;

UNION ALL

The difference between UNION and UNION ALL is,

UNION - Eliminates duplicate rows exists between SELECT querie, And also sorts the records.
UNION ALL - Do NOT eliminates dublicate rows.


INTERSECT

INTERSECT query also combines result set of two or more SELECT quries but retuns only COMMON rows amoung result sets.

Note : Each SELECT quries within the INTERSEC query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> INTERSECT SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 INTERSECT SELECT * FROM emp where dept=20;

SQL> select * from hr.emp INTERSECT select * from sales.emp;

Here, it combines emp tables from hr and sales schemas but returns only COMMON records between two tables.

Note that, need adequate privileages to select records from other schemas.

Example with 3 select quuries.

SQL> select name from hr.emp INTERSECT select name from sales.emp INTERSECT select name from purchase.emp;

MINUS

MINUS returns all rows in the first query that are not returned by the second query.

Note : Each SELECT quries within the MINUS query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> UNION SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 MINUS SELECT * FROM emp where dept=20;

SQL> select * from hr.emp MINUS select * from sales.emp;

Restrictions on the Set Operators The set operators are subject to the following restrictions:

• 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.

• You cannot also specify the for_update_clause with the set operators.

• You cannot specify the order_by_clause in the subquery of these operators.

• You cannot use these operators in SELECT statements containing TABLE collection expressions.