You can arbitrarily put parentheses around queries, or use the parentheses to control
the order of evaluation of the INTERSECT, EXCEPT, or UNION operations. These
operations are evaluated from left to right when no parentheses are present, with the
exception of INTERSECT operations, which would be evaluated before any UNION or
EXCEPT operations.

Duplicates in UNION, INTERSECT, and EXCEPT ALL results

The ALL and DISTINCT keywords determine whether duplicates are eliminated from the
result of the operation. If you specify the DISTINCT keyword, then the result will have
no duplicate rows. If you specify the ALL keyword, then there may be duplicates in the
result, depending on whether there were duplicates in the input. DISTINCT is the default,
so if you don't specify ALL or DISTINCT, the duplicates will be eliminated. For example,
UNION builds an intermediate ResultSet with all of the rows from both queries and
eliminates the duplicate rows before returning the remaining rows. UNION ALL returns all
rows from both queries as the result.

Depending on which operation is specified, if the number of copies of a row in the left
table is L and the number of copies of that row in the right table is R, then the number of
duplicates of that particular row that the output table contains (assuming the ALL keyword
is specified) is:

· UNION: ( L + R ).
· EXCEPT: the maximum of ( L - R ) and 0 (zero).
· INTERSECT: the minimum of L and R.

Examples

-- a Select expression
SELECT *
FROM ORG

-- a subquery
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS

-- a subquery
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS (CLASS_CODE)

-- a UNION
-- returns all rows from columns DEPTNUMB and MANAGER
-- in table ORG
-- and (1,2) and (3,4)
-- DEPTNUMB and MANAGER are smallint columns
SELECT DEPTNUMB, MANAGER
FROM ORG
UNION ALL