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.
Examples
- Example 1: 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.
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.
- Example: 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.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'.