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