Ways to merge lists of values

There are several ways to use the UNION keyword for merging lists of values.

A union is an SQL operation that combines the results of two SELECT statements to form a single result table. When DB2® encounters the UNION keyword, it processes each SELECT statement to form an interim result table. DB2 then combines the interim result table of each statement. If you use UNION to combine two columns with the same name, the corresponding column of the result table inherits that name.

You can use the UNION keyword to obtain distinct rows in the result table of a union, or you can use UNION with the optional keyword ALL to obtain all rows, including duplicates.

How to eliminate duplicates

Use UNION to eliminate duplicates when merging lists of values that are obtained from several tables. The following example combines values from the EMP table and the EMPPROJACT table.

Example 1: List the employee numbers of all employees for which either of the following statements is true:
  • The department number of the employee begins with 'D'.
  • The employee is assigned to projects whose project numbers begin with 'MA'. 
SELECT EMPNO FROM EMP
  WHERE DEPT LIKE 'D%'
  UNION
  SELECT EMPNO FROM EMPPROJACT
  WHERE PROJNO LIKE 'MA

The result table looks like the following example:

EMPNO
======
000010
000020
000060
000200
000220

The result is the union of two result tables, one formed from the EMP table, the other formed from the EMPPROJACT table. The result, a one-column table, is a list of employee numbers. The entries in the list are distinct.

How to retain duplicates

If you want to keep duplicates in the result of a union, specify the optional keyword ALL after the UNION keyword.

Example 1: Replace the UNION keyword in the previous example with UNION ALL: 
SELECT EMPNO FROM EMP
  WHERE DEPT LIKE 'D%'
  UNION ALL
  SELECT EMPNO FROM EMPPROJACT
  WHERE PROJNO LIKE 'MA

The result table looks like the following example:

EMPNO
======
000220
000200
000060
000010
000020
000010

Now, 000010 is included in the list more than once because this employee works in a department that begins with 'D' and also works on a project that begins with 'MA'.