Set Operators

There are situations when we need to combine the results from two or more SELECT statements. SQL enables us to handle these requirements by using set operations. 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. Oracle SQL supports the following four set operations:

• UNION ALL

• UNION

• MINUS

• INTERSECT

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

 1. The result sets of both the queries must have the same number of columns.

 2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

These conditions are also referred to as union compatibility conditions. The term union compatibility is used even though these conditions apply to other set opera tions as well. Set operations are often called vertical joins, because the result com- bines data from two or more SELECTS based on columns instead of rows. The generic syntax of a query involving a set operation is:

<component query>

{UNION | UNION ALL | MINUS | INTERSECT}

<component query>

The following list briefly describes the four set operations supported by Oracle SQL:

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

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

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

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