UNION basics in DB2 SQL

The fullselect and set operators

A fullselect is one subselect or several subselects connected by set operators. Those operators are:

UNION is not a join. Join output can contain columns from both tables on the same row. UNION output is always a row that already existed in the first query or the second. You use it to merge lists: employees in two tables, codes from a current file and an archive, or several mutually exclusive WHERE branches.

UNION

UNION without ALL derives a result from R1 and R2: every row that appears in either table, with duplicate rows eliminated.

1
2
3
4
5
6
7
SELECT EMPNO
  FROM DSN8C10.EMP
 WHERE WORKDEPT LIKE 'D%'
 UNION
SELECT EMPNO
  FROM DSN8C10.EMPPROJACT
 WHERE PROJNO LIKE 'MA%';

IBM describes this as merging lists and keeping distinct employee numbers. If the same EMPNO is in a D-department and on an MA-project, it appears once.

Duplicate test: two rows are duplicates when each pair of corresponding values is equal. Two nulls are considered equal for this purpose. That differs from WHERE COL = COL, which is unknown when COL is null.

UNION ALL

UNION ALL returns all rows of R1 and all rows of R2. If three rows match in both queries, you see six rows (three copies from each side), not three.

1
2
3
4
5
6
7
SELECT EMPNO
  FROM DSN8C10.EMP
 WHERE WORKDEPT LIKE 'D%'
 UNION ALL
SELECT EMPNO
  FROM DSN8C10.EMPPROJACT
 WHERE PROJNO LIKE 'MA%';

Prefer UNION ALL when:

• The two WHEREs are disjoint (a row cannot qualify for both)
• You want duplicates (for example stacking monthly fact tables to count events)
• You will aggregate afterward and duplicates are harmless or meaningful

UNION without ALL must detect duplicates, which usually means extra sort or hash work and can forbid some long string types in older rules. UNION ALL is the form used inside EXISTS examples that concatenate MONTH1, MONTH2, and MONTH3: duplicates would not change “at least one row exists,” and ALL avoids distinct processing.

Some types (LOB columns, certain long strings) are restricted in set operations that are not UNION ALL. If a UNION of LOB-bearing subselects fails, try UNION ALL if duplicate elimination was not required, or project a non-LOB key instead.

Column compatibility rules

For any set operator:

• Same degree — R1 and R2 must have the same number of columns
• Compatible corresponding columns — column n of R1 and column n of R2 follow the same result-type rules used for CASE, COALESCE, and IN-list expressions
• Names — if both sides use the same result column name, the output column inherits it; otherwise the name may be generated and unusable in ORDER BY by name (use DESCRIBE / SQLNAME or ordinal positions)

When more than two queries are chained, Db2 resolves types left to right: type(R1 ∪ R2) is then combined with R3. CHAR(2) UNION CHAR(4) UNION VARCHAR(3) does not jump straight to a single guess; intermediates matter.

Character conversion and CCSID rules for set operations are documented separately: if the two string columns have different CCSIDs, Db2 converts according to the combining rules and the result CCSID may be VARCHAR even if both inputs were CHAR.

1
2
3
4
5
6
7
8
9
-- Invalid: 2 columns UNION 1 column
SELECT EMPNO, LASTNAME FROM DSN8C10.EMP
 UNION
SELECT EMPNO FROM DSN8C10.EMPPROJACT;
 
-- Valid: pad the second select with a typed null or literal
SELECT EMPNO, LASTNAME FROM DSN8C10.EMP
 UNION
SELECT EMPNO, CAST(NULL AS VARCHAR(15)) FROM DSN8C10.EMPPROJACT;

Use CAST when you need a null placeholder so the types stay compatible. A character literal '' is not the same as a typed null and is not a substitute for a missing numeric column.

Duplicates and ORDER BY with UNION

Duplicates

UNION’s distinct step looks at entire rows of the combined projection, not a hidden primary key. If you UNION EMP and EMPHIST on (NBR, NAM, DPT) you collapse people who match on those three columns. If you also select SAL, the 2010 salary and 2009 salary differ, so both rows survive—the rows are not duplicates.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT NBR, NAM, DPT
  FROM EMP
 UNION
SELECT NBR, NAM, DPT
  FROM EMPHIST
 ORDER BY NBR;
 
-- Including SAL keeps overlapping employees twice when pay changed
SELECT NBR, NAM, DPT, SAL
  FROM EMP
 UNION
SELECT NBR, NAM, DPT, SAL
  FROM EMPHIST
 ORDER BY NBR;

ORDER BY

Write one ORDER BY for the whole fullselect, after the last SELECT. Sorting a single leg does not sort the stacked result.

1
2
3
4
5
6
7
8
SELECT EMPNO, LASTNAME AS NAME, 'EMP' AS SRC
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 UNION ALL
SELECT EMPNO, LASTNAME, 'ARCH'
  FROM EMP_ARCHIVE
 WHERE WORKDEPT = 'A00'
 ORDER BY NAME, EMPNO;

ORDER BY NAME uses the first select list’s AS alias. The second SELECT’s column name LASTNAME does not have to match, but the first name is what ORDER BY name resolution uses. You can also ORDER BY 2, 1 (column numbers). FETCH FIRST n ROWS ONLY after the union limits the combined output, not each branch.

Parenthesize when mixing operators or when a subselect needs its own FETCH/ORDER inside a nested table expression. Operator precedence for set operators should be made obvious with parentheses in production SQL: (A UNION B) EXCEPT C is not A UNION (B EXCEPT C).

EXCEPT

EXCEPT is set difference: rows in the first result that do not have a corresponding row in the second. Some products call this MINUS. Without ALL, duplicate rows are eliminated from the result. EXCEPT ALL treats duplicates as significant (multiset difference).

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Items in TABLE1 that are not in TABLE2
SELECT ITEM FROM TABLE1
EXCEPT
SELECT ITEM FROM TABLE2;
 
-- Customers in the USA who are not also in EMP (by name columns)
SELECT LAST_NAME, FIRST_NAME, CUST_NUM
  FROM CUST
 WHERE COUNTRY = 'USA'
EXCEPT
SELECT LAST_NAME, FIRST_NAME, EMP_NUM
  FROM EMP
 WHERE COUNTRY = 'USA';

Corresponding columns still must be compatible. EMP_NUM and CUST_NUM must share a type family even if the business meanings differ—you are comparing the projected values, not the column names. Nullability: if the first operand is NOT NULL, the EXCEPT result is NOT NULL (rows can only come from the first operand).

EXCEPT is often clearer than NOT IN when comparing whole rows or several columns. For a single nullable key, NOT EXISTS remains a common alternative.

INTERSECT

INTERSECT keeps rows that appear in both results. Without ALL, the result is distinct. INTERSECT ALL keeps duplicates according to how many times the row appears in both sides.

1
2
3
4
5
6
7
8
-- USA customers who are also employees (matching the projected columns)
SELECT LAST_NAME, FIRST_NAME, CUST_NUM
  FROM CUST
 WHERE COUNTRY = 'USA'
INTERSECT
SELECT LAST_NAME, FIRST_NAME, EMP_NUM
  FROM EMP
 WHERE COUNTRY = 'USA';

If either operand disallows nulls, INTERSECT’s result disallows nulls (a null could not appear in both as a real intersection of NOT NULL data in the usual sense). Use INTERSECT when the English is “in A and also in B.” Use a join when you also need columns that exist on only one side.

UNION in subqueries

A fullselect with UNION can be a scalar subquery only if the combined result is still one column and one row. IBM’s SECRET-project example UNIONs two RESPEMP lookups so duplicates of the same employee collapse to one value for EMPNO = ( … UNION … ). If two different employees remain, -811 still occurs.

Inside EXISTS, UNION ALL of several month tables is a standard pattern: EXISTS cares about non-empty, not uniqueness.

Explain It Like I'm Five

UNION is pouring two boxes of crayons into one box and throwing away extra crayons that are the same color. UNION ALL is dumping both boxes in and keeping every crayon, even if you now have three reds. EXCEPT is “crayons in my box that you don’t have.” INTERSECT is “crayons we both have.” The boxes must have the same kind of slots (same number of columns, compatible types) or the crayons will not stack.

Exercises

1. Write UNION of EMPNO from EMP where JOB = 'CLERK' and EMPNO from EMPPROJACT for project 'AD3100'. Then change it to UNION ALL and predict extra rows.
2. Explain why adding a column that differs on overlapping people makes UNION keep both rows.
3. Fix a 2-column SELECT UNION 1-column SELECT using CAST(NULL AS …) on the shorter side.
4. Write EXCEPT to list WORKDEPT values in EMP that do not appear as DEPTNO in DEPT (orphan department codes).
5. Place ORDER BY correctly on a three-way UNION ALL and explain why ORDER BY inside the first SELECT alone would not order the final report.