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

See Also