Results:

NAME      EXPRESSION 1
--------- ------------
BURKE     EMPLOYEE
GASPARD   APPLICANT
JACOBS    APPLICANT

The portion of the query that selects from Q.STAFF also creates a column in the report with the constant EMPLOYEE in it. The portion of the query that selects from Q.APPLICANT does the same with the constant APPLICANT. A default column name is assigned to that column, but can easily be changed on the form.

In any query, the lengths of the columns are matched. In the previous example, EMPLOYEE is padded with a blank to match the length of APPLICANT.

The next example selects from Q.STAFF and Q.INTERVIEW all the managers and the people they interviewed:

SELECT NAME, '             '
FROM Q.STAFF, Q.INTERVIEW
WHERE MANAGER = ID
UNION
SELECT NAME, 'NO INTERVIEWS'
FROM Q.STAFF
WHERE JOB = 'MGR'
  AND ID NOT IN (SELECT MANAGER FROM Q.INTERVIEW)

Results:

   NAME       EXPRESSION 1
   ---------  -------------
   DANIELS    NO INTERVIEWS
   FRAYE
   HANES
   JONES      NO INTERVIEWS
   LEA
   LU         NO INTERVIEWS
   MARENGHI   NO INTERVIEWS
   MOLINARE
   PLOTZ
   QUILL
   SANDERS

Retain duplicates in UNION

UNION implies that only DISTINCT rows are selected from the columns named in both SELECT statements.

If you want to keep duplicates in the result of a UNION operation, specify the optional keyword ALL after UNION. When UNION ALL is specified, duplicate rows are not eliminated from the result.

The following example selects all sales people in Q.STAFF who have been employed for more than five years, or who earn a commission greater than $850. The sales people who meet both conditions appear twice in the resulting report:

SELECT * FROM Q.STAFF
WHERE JOB = 'SALES' AND YEARS > 5
UNION ALL
SELECT * FROM Q.STAFF
WHERE JOB = 'SALES' AND COMM > 850
ORDER BY 2

Produces this report:

   ID  NAME         DEPT  JOB     YEARS      SALARY        COMM
------ ---------  ------  -----  ------  ----------  ----------
  340  EDWARDS        84  SALES       7    17844.00     1285.00
  340  EDWARDS        84  SALES       7    17844.00     1285.00
  310  GRAHAM         66  SALES      13    21000.00      200.30
   90  KOONITZ        42  SALES       6    18001.75     1386.70
   90  KOONITZ        42  SALES       6    18001.75     1386.70
   40  O'BRIEN        38  SALES       6    18006.00      846.55
   20  PERNAL         20  SALES       8    18171.25      612.45
   70  ROTHMAN        15  SALES       7    16502.83     1152.00
   70  ROTHMAN        15  SALES       7    16502.83     1152.00
  220  SMITH          51  SALES       7    17654.50      992.80
  220  SMITH          51  SALES       7    17654.50      992.80
  150  WILLIAMS       51  SALES       6    19456.50      637.65
  280  WILSON         66  SALES       9    18674.50      811.50

If UNION rather than UNION ALL is specified, determining which sales people satisfied both conditions requires closer inspection, as shown in this report:

   ID  NAME         DEPT  JOB     YEARS      SALARY        COMM
------ ---------  ------  -----  ------  ----------  ----------
  340  EDWARDS        84  SALES       7    17844.00     1285.00
  310  GRAHAM         66  SALES      13    21000.00      200.30
   90  KOONITZ        42  SALES       6    18001.75     1386.70
   40  O'BRIEN        38  SALES       6    18006.00      846.55
   20  PERNAL         20  SALES       8    18171.25      612.45
   70  ROTHMAN        15  SALES       7    16502.83     1152.00
  220  SMITH          51  SALES       7    17654.50      992.80
  150  WILLIAMS       51  SALES       6    19456.50      637.65
  280  WILSON         66  SALES       9    18674.50      811.50

The order of evaluation of each subquery has no effect on the result of the operation. However, when you use UNION ALL and UNION to combine two SELECT queries, the result of the operation depends on the order of evaluation. Parentheses are resolved first, starting with the innermost one. Then, each clause is resolved from left to right.

For example, the following queries yield different results:

• In this example, all rows of TABLE1 are merged with all rows of TABLE2 to form an intermediate table, which is merged with TABLE3 with the elimination of duplicates.

  (TABLE1 UNION ALL TABLE2) UNION TABLE3

• In this example, all rows of TABLE2 are merged with TABLE3 with the elimination of duplicates, to form an intermediate table that is merged with all rows of TABLE1.

  TABLE1 UNION ALL (TABLE2 UNION TABLE3)

Rules for using UNION

• You can put UNION between two SELECT statements only if the two statements select the same number of columns and the corresponding columns are compatible data types (for example, numeric to numeric or string to string).
• Corresponding columns in select statements merged by UNION do not need to have the same name. Because the names of the interleaved columns are likely to be different, do not use a column name after an ORDER BY. Instead, always use a column number, such as ORDER BY 1.
• The lengths and data types of the columns named in the SELECT statements only need to be comparable. They must both be either numeric, character, graphic, date, time, or timestamp. They cannot be a combination of these groups. For example:

  SELECT ID
  
  ·
  ·
  ·
  
  UNION
  SELECT DEPT
  
  ·
  ·
  ·
  

  If ID is CHAR(6) and DEPT is CHAR(3), the column in the result table is CHAR(6). The values in the resulting table that are derived from DEPT are padded on the right with blanks.

When to use UNION -- when to join

When to use UNION to merge tables and when to join tables depends on what kind of results you want in your report.

• UNION interleaves rows from two queries into one report.
• Joining tables does not interleave the rows, but joins each row from one table horizontally to each row from another table. When joining, it is essential that you use a condition to limit the number of combinations so that every row is not joined to every other row.

The following query does not produce a report that is as readable or meaningful as the UNION query in UNION. Because no common column was used in the WHERE condition in this query to join the two tables, the report contains duplicates.

This query:

SELECT S.NAME, 'EMPLOYEE ', A.NAME, 'APPLICANT'
FROM Q.STAFF S, Q.APPLICANT A
WHERE YEARS < 3 AND EDLEVEL > 14

Produces this report:

NAME      EXPRESSION 1  NAME1      EXPRESSION 2
--------- ------------  ---------  ------------
BURKE     EMPLOYEE      JACOBS     APPLICANT
BURKE     EMPLOYEE      GASPARD    APPLICANT

You can also use UNION between two SELECT statements that refer to the same table. For example, to list all employees by number within department, and identify those with ten years of service:

SELECT DEPT, ID, NAME, YEARS, 'TEN YEARS'
FROM Q.STAFF
WHERE YEARS = 10
 UNION
SELECT DEPT, ID, NAME, YEARS, '        '
FROM Q.STAFF
WHERE NOT YEARS = 10
ORDER BY 1, 2