UNION

UNION merges the rows of two or more tables into one report. To make sense, these rows should relate to one another, have the same width, and have the same data type.

Using UNION, you can merge values from two or more tables into the same columns (but different rows) of the same report. You can use UNION more than once in a query.

Examples in this topic that use UNION ALL require enhanced UNION support.

The following example selects the name and employee columns from Q.STAFF and the name and applicant columns from Q.APPLICANT.

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

The query produces this report:

NAME       COL1      
---------  --------- 
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 panels.

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

The following 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)

The query produces this report:

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

Retaining duplicate rows with 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 salespeople in Q.STAFF who have been employed for more than five years, or who earn a commission greater than $850. The salespeople who meet both conditions appear twice in the resulting report.

This query:

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 salespeople satisfied both conditions requires closer inspection, as shown in the report in the following figure:

Figure 1. An example of the results of the UNION statement

   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 or 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 set. 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).
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. The columns must both have numeric, character, graphic, date, time, or timestamp values. They cannot be a combination of these data types.
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 versus when to join tables

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 (a WHERE clause) 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. 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       COL1      NAME2      COL3     
---------  --------  ---------  ---------
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, use a query like the following:

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