Query UNIONUNION ALL

A query creates a virtual table based on existing tables or constants built into tables.

Syntax{ ( Query ) | 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) -- 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')