Chapter 7. Set Operations
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:
The result sets of both the queries must have the same number of
columns.
The datatype of each column in the second result set must match the
datatype of its corresponding column in the first result set.
 |
The datatypes do not need to be the same if those in the second
result set can be automatically converted by Oracle (using implicit
casting) to types compatible with those 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 operations as
well. Set operations are often called
vertical
joins, because the result combines 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 keywords UNION, UNION ALL, MINUS, and INTERSECT are
set operators. We can have more than
two component queries in a composite query; we will always use one
less set operator than the number of component queries.
The following sections discuss syntax, examples, rules, and
restrictions for the four set operations.
|
