Query

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

Syntax

{
    ( Query ) |
    Query UNION [ALL] Query |
    SelectExpression
    VALUES expression
}

You can arbitrarily put parentheses around queries, or use the parentheses to control the order of evaluation of UNION operations. UNION operations are evaluated from left to right when no parentheses are present.

You can combine two queries into one using the UNION [ALL] operation. 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.

Examples

-- a Select expression  
SELECT *
FROM 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');