SELECT DISTINCT in DB2 for z/OS

ALL versus DISTINCT

The select-clause syntax is:

1
2
SELECT [ ALL | DISTINCT ] select-list
  FROM ...

• ALL — retain all rows of the final result table; do not eliminate redundant duplicates. This is the default if you write neither word.
• DISTINCT — eliminate all but one of each set of duplicate rows of the final result table.

1
2
3
4
5
6
7
-- Every employee’s department, with repeats
SELECT WORKDEPT
  FROM HR.EMPLOYEE;
 
-- Each department value once
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE;

If 40 employees work in A00, the first query returns A00 forty times. The second returns A00 once. That is the whole beginner idea—then the details start.

What DISTINCT removes

DISTINCT looks at the entire select list, not “the first column” and not the base table’s primary key. Two result rows are duplicates only if each value in the first is equal to the corresponding value in the second.

1
2
3
4
5
6
7
-- Unique department *and* job combinations
SELECT DISTINCT WORKDEPT, JOB
  FROM HR.EMPLOYEE;
 
-- Unique last names (two people named SMITH collapse to one row)
SELECT DISTINCT LASTNAME
  FROM HR.EMPLOYEE;

SELECT DISTINCT WORKDEPT, JOB still shows A00 twice if A00 has both CLERK and MANAGER. Beginners who wanted “unique departments” must not add extra columns they do not want in the uniqueness key. Extra columns make more unique combinations.

Expressions and literals participate too. SELECT DISTINCT WORKDEPT, 'X' is unique on department only, because the literal is the same on every row. SELECT DISTINCT YEAR(HIREDATE) unique-ifies the computed year, not the raw date.

DISTINCT may appear more than once in a subselect: on the SELECT clause, inside a column function such as COUNT(DISTINCT …), and in subqueries. Each occurrence has its own meaning. SELECT DISTINCT and COUNT(DISTINCT col) are not interchangeable.

COUNT(DISTINCT) is a different tool

1
2
3
4
5
SELECT COUNT(DISTINCT WORKDEPT) AS DEPT_COUNT
  FROM HR.EMPLOYEE;
 
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE;

The first returns one number: how many different non-null department values exist. The second returns one row per department value. COUNT(DISTINCT col) ignores nulls in col for the count. SELECT DISTINCT WORKDEPT keeps a single null-group row if any WORKDEPT is null (see below).

NULL handling with DISTINCT

In a WHERE comparison, NULL = NULL is not TRUE; it is UNKNOWN. DISTINCT uses a different rule. The SQL Reference states: for determining duplicates, two null values are considered equal.

If five employees have a null WORKDEPT, SELECT DISTINCT WORKDEPT returns one row for that null, not five. That matches how GROUP BY puts all nulls in one group, and how a unique index treats nulls as equal unless WHERE NOT NULL is specified on the index.

1
2
3
4
5
6
7
8
-- One result row for NULL WORKDEPT, plus one per actual department
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE;
 
-- WHERE still does not treat nulls as equal
SELECT EMPNO
  FROM HR.EMPLOYEE E1
 WHERE E1.WORKDEPT = E1.WORKDEPT;  -- null departments still fail this

If you need WHERE to treat two nullable columns as equal including both-null, use IS NOT DISTINCT FROM, not the DISTINCT keyword on SELECT. SELECT DISTINCT and the DISTINCT predicate are related ideas (nulls can be “the same”) but different syntax.

1
2
3
SELECT E.EMPNO
  FROM HR.EMPLOYEE E
 WHERE E.WORKDEPT IS NOT DISTINCT FROM E.JOB;  -- true if both null or both equal

CHAR padding still matters. CHAR(3) 'A00' and CHAR(5) 'A00 ' may or may not compare equal depending on comparison rules (blank padding). DISTINCT uses those same equality rules for non-null values. Mixed CCSID conversions can also make values that “look the same” on a screen compare as different. When DISTINCT “fails” to collapse rows, HEX the columns before rewriting the query.

DISTINCT versus GROUP BY

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Unique departments
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE;
 
-- Same unique departments, GROUP BY style
SELECT WORKDEPT
  FROM HR.EMPLOYEE
 GROUP BY WORKDEPT;
 
-- GROUP BY earns its keep with an aggregate
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT, AVG(SALARY) AS AVG_SAL
  FROM HR.EMPLOYEE
 GROUP BY WORKDEPT;

The first two queries can return the same department list. Db2’s optimizer may even choose similar access paths (sort unique, index unique scan, hash unique). Write the form that matches your intent:

• I want unique rows of these expressions — SELECT DISTINCT
• I want one row per group with totals — GROUP BY

Mixing DISTINCT with GROUP BY in the same subselect is usually a smell. If you already group, the select list is already unique on the grouping columns. DISTINCT on top does extra uniqueness work for no benefit unless the select list contains extra non-grouped expressions that your shop’s SQL rules should not allow anyway.

UNION (not UNION ALL) also eliminates duplicates across combined queries. UNION DISTINCT is the same idea at set-operation level. Prefer UNION ALL when you know there are no duplicates or you want to keep them—duplicate removal is never free.

Performance considerations

DISTINCT is a correctness keyword first. It is not a magic “make my join faster” switch. To remove duplicates, Db2 must discover uniqueness. Typical costs:

• Sort unique — sort the result and collapse adjacent duplicates. Sorts use work files, memory, and CPU. Wide rows (many VARCHAR columns) make the sort heavier.
• Hash unique — build a hash table of combinations already seen. Memory and CPU still scale with the number of distinct values.
• Index assistance — if an index already guarantees the select list is unique, or if an index can return already-ordered unique keys, Db2 may avoid a separate uniqueness operator. EXPLAIN is the way to see which choice you got.
• Bad DISTINCT hiding a bad join — a missing join predicate creates a cartesian product; DISTINCT then tries to crush the explosion. Fix the join. Do not DISTINCT-wash a fan-out bug.

1
2
3
4
5
6
7
8
9
-- Cheap if WORKDEPT is leading in an index and you only need the key
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE;
 
-- Expensive pattern: join that multiplies rows, then DISTINCT to clean up
SELECT DISTINCT E.EMPNO, E.LASTNAME
  FROM HR.EMPLOYEE E,
       HR.EMPPROJACT P
 WHERE E.EMPNO = P.EMPNO;

If you only needed employees who have at least one project activity, EXISTS or IN (subquery) or a semi-join pattern is often clearer and can stop at the first match instead of building a huge intermediate result and sorting it.

Older Db2 releases restricted DISTINCT when a result string column’s maximum length was greater than 254 bytes. Current Db2 12 and 13 for z/OS do not carry that old 254-byte SELECT DISTINCT limit in the same way—you can DISTINCT longer strings—but DISTINCT on huge VARCHAR or CLOB-like results is still a performance and resource problem. Do not DISTINCT a CLOB “just in case.”

RUNSTATS still matter. The optimizer estimates how many distinct values a column has (COLCARDF). Stale statistics make it pick a sort when a cheaper path existed, or the reverse. After large data changes, statistics and EXPLAIN belong in the same conversation as DISTINCT.

Practical patterns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- List of departments that actually have employees
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE
 WHERE WORKDEPT IS NOT NULL
 ORDER BY WORKDEPT;
 
-- Distinct expression
SELECT DISTINCT SUBSTR(LASTNAME, 1, 1) AS INITIAL
  FROM HR.EMPLOYEE
 ORDER BY INITIAL;
 
-- Distinct inside an aggregate (one row result)
SELECT COUNT(DISTINCT JOB) AS JOB_KINDS,
       COUNT(*)            AS EMP_ROWS
  FROM HR.EMPLOYEE;

ORDER BY is independent: DISTINCT decides which rows survive; ORDER BY sorts the survivors. You may ORDER BY a select-list number or an AS name. You may not DISTINCT on one set of columns and secretly unique another—the select list is the uniqueness key.

DISTINCT with ORDER BY, FETCH, and set operations

DISTINCT runs as part of building the result table of the subselect. ORDER BY then sorts that already-unique result. You can ORDER BY columns that are in the select list, by ordinal position, or by an AS name. Ordering by a column you did not select is restricted compared to SELECT ALL; if uniqueness already collapsed the row, there may be no single “other column” value to sort on. Keep the sort keys in the select list when you use DISTINCT, or sort on the distinct expressions themselves.

1
2
3
SELECT DISTINCT WORKDEPT
  FROM HR.EMPLOYEE
 ORDER BY WORKDEPT;

FETCH FIRST n ROWS ONLY (or LIMIT in some client dialects mapped to Db2) applies to the statement result. Combined with DISTINCT, “first 10 unique departments” depends on whether you ORDER BY. Without ORDER BY, which 10 unique values you get is not a business ordering—it is whatever access path survived. Always ORDER BY when the “top n distinct” list must be stable.

UNION already removes duplicates between its legs (UNION ALL does not). Writing SELECT DISTINCT on each UNION leg and then UNION is duplicate work. Prefer UNION ALL of already-distinct sets when you can prove there is no overlap, or a single UNION when you need one uniqueness pass. EXPLAIN both if the sets are large.

DISTINCT does not replace a unique constraint. If the business rule is “one row per department in this table,” enforce it with a unique index or PRIMARY KEY, not with SELECT DISTINCT in every report. DISTINCT is a query-time bandage; constraints prevent the duplicates from being stored.

When you do not need DISTINCT

Skip DISTINCT when the select list is already unique: selecting a primary key, selecting from a table with a unique index on those columns, or selecting a GROUP BY list. The optimizer may notice uniqueness and skip a sort, but you still made the statement harder to read. Skip DISTINCT when duplicates are real and the caller wants every event row. Skip DISTINCT as a join repair. Use it when the user asked for a list of unique values and you have measured that the uniqueness operator is acceptable.

In COBOL, SELECT DISTINCT still returns a result table you FETCH row by row. It does not change host-variable layouts except that you see fewer rows. If the program needed every duplicate for an audit trail, DISTINCT is the wrong clause even if the screen looks tidier.

Explain It Like I'm Five

Imagine a roll call where every child shouts their favorite color. SELECT ALL writes down every shout, so “blue” appears twelve times. SELECT DISTINCT writes each color once. If two children refuse to pick a color (NULL), DISTINCT still writes one line that says “no color,” because for this game two silent answers count as the same. GROUP BY is a different game where you also tally how many children picked each color. DISTINCT does not tally; it only crosses out repeats. Crossing out repeats takes extra work when the list is long, so you only ask for it when you truly want a unique list.

Exercises

1. Write SELECT DISTINCT JOB from an employee table and predict whether two people with the same job produce one row or two.
2. Write SELECT DISTINCT WORKDEPT, JOB and explain why it can return more rows than SELECT DISTINCT WORKDEPT.
3. If three rows have WORKDEPT null, how many rows does SELECT DISTINCT WORKDEPT return for those nulls?
4. Rewrite a unique-department query with GROUP BY. Add COUNT(*) and explain why DISTINCT alone cannot give you that count per department.
5. Describe a join where DISTINCT would hide a missing join predicate, and name a better rewrite (EXISTS or an extra ON condition).