A set operation combines
tables or views vertically (one on top of the other) by combining
the results of two queries. A set operation is a SELECT statement
that contains
-
two queries (each beginning with
a SELECT clause)
-
one of the set operators EXCEPT,
INTERSECT, UNION, and OUTER UNION
-
one or both of the keywords ALL
and CORR (CORRESPONDING) as modifiers.
A single SELECT statement
can contain multiple set operations.
When processing a set
operation that displays only unique rows (a set operation that contains
the set operator EXCEPT, INTERSECT, or UNION), PROC SQL makes two
passes through the data, by default. For set operations that display
both unique and duplicate rows, only one pass through the data is
required.
For the set operators
EXCEPT, INTERSECT, and UNION, columns are overlaid based on the relative
position of the columns in the SELECT clause rather than by column
name. In order to be overlaid, columns in the same relative position
in the two SELECT clauses must have the same data type.
One or both keywords
can be used to modify the default action of a set operator.