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

Parent topic: SQL queries