Query
A Query creates a virtual table based on existing tables or constants built into tables.
Syntax
{ ( Query [ ORDER BY clause ] [ result offset clause ] [ fetch first clause ] ) | Query EXCEPT [ ALL | DISTINCT ] Query | Query UNION [ ALL | DISTINCT ] Query | VALUES Expression }
You can arbitrarily put parentheses around queries, or use the
parentheses to control the order of evaluation of the UNION
operations. These operations are evaluated from left to right when no
parentheses are present.
Duplicates in UNION 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).
Examples
Here’s a simple SELECT
expression:
SELECT *
FROM ORG;
Here’s a SELECT
with a subquery:
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS;
Here’s a SELECT
with a subquery:
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS;
Here’s a UNION
that lists all employee numbers from certain
departments who are assigned to specified project numbers:
SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO, 'emp_act'
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112');