|
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 INTERSECT [ ALL | DISTINCT ] Query |
Query EXCEPT [ ALL | DISTINCT ] Query |
Query UNION [ ALL | DISTINCT ] Query |
SelectExpression | VALUES Expression
}
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 resultsThe
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
VALUES (1,2), (3,4)
-- a values expression
VALUES (1,2,3)
-- Use of ORDER BY and FETCH FIRST in a subquery
SELECT DISTINCT A.ORIG_AIRPORT, B.FLIGHT_ID FROM
(SELECT FLIGHT_ID, ORIG_AIRPORT
FROM FLIGHTS
ORDER BY ORIG_AIRPORT DESC
FETCH FIRST 40 ROWS ONLY)
AS A, FLIGHTAVAILABILITY AS B
WHERE A.FLIGHT_ID = B.FLIGHT_ID
-- List the employee numbers (EMPNO) of all employees in the EMPLOYEE
-- table whose department number (WORKDEPT) either begins with 'E' or
-- who are assigned to projects in the EMP_ACT table
-- whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'
SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- Make the same query as in the previous example
-- and "tag" the rows from the EMPLOYEE table with 'emp' and
-- the rows from the EMP_ACT table with 'emp_act'.
-- Unlike the result from the previous example,
-- this query may return the same EMPNO more than once,
-- identifying which table it came from by the associated "tag"
SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO, 'emp_act' FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- Make the same query as in the previous example,
-- only use UNION ALL so that no duplicate rows are eliminated
SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION ALL
SELECT EMPNO
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- Make the same query as in the previous example,
-- only include an additional two employees currently not in any table
-- and tag these rows as "new"
SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO, 'emp_act'
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
UNION
VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')
|
