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');