Union operators emulate
the behavior of three SQL set operations:
UNION, INTERSECT,
and EXCEPT.
Union operators combine two data sets that have column schemas
that match, and have compatible data types, into a single data set.
The new set of rows is created according to one
of three rules:
- UNION
- Unconditionally merges two sets of input
rows into a single output
data set.
- INTERSECT
- Merges two sets of
input rows into a single output data set and
retains only those rows that are common to both inputs.
- EXCEPT
- Merges two sets of input rows into a single output data set and
retains only those rows that exist in the first data set but not the
second data set.
Three other choices, UNION ALL, EXCEPT ALL, and INTERSECT ALL, operate the same as above except that a row which exists in both input data sets appears twice in the output data set.
Example
An input data set from table 1 contains the following rows:
COL1 COL2 a a a b a c
The second input data set from table 2, contains the following
rows:
COL1 COL2 a b a c a dA UNION operation merges the two data sets tables produces four rows:
COL1 COL2 a a a b a c a d
An INTERSECT operation merges
the same two input tables produces two rows:
COL1 COL2 a b a cAn EXCEPT operation merges the tables to produce one row:
COL1 COL2 a aAn EXCEPT operation with input tables reversed produces a different row:
COL1 COL2 a d