IN and NOT IN predicates in DB2 SQL

What IN tests

IBM’s SQL Reference defines the IN predicate as comparing a value or values with a set of values. When you specify a single expression on the left, the set is either a parenthesized list of expressions or a fullselect that returns one column and any number of rows. Data types of the left expression and the list items / subquery column must be compatible. If the operands have different types or string CCSIDs, Db2 applies the same result-type and string-conversion rules used for UNION.

Like every predicate, IN is TRUE, FALSE, or UNKNOWN. WHERE and HAVING keep only TRUE.

IN with a value list

The list form is the one you write on day one of SQL:

1
2
3
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM DSN8C10.EMP
 WHERE WORKDEPT IN ('D11', 'B01', 'C01');

IBM’s sample wording: the predicate is true for any row whose employee is in department D11, B01, or C01. Each list item is an expression: a constant, a host variable, a special register, or a more complex expression (IBM’s YEAR(CURRENT DATE) example).

1
2
3
4
5
SELECT PROJNO, PRENDATE
  FROM DSN8C10.PROJ
 WHERE YEAR(PRENDATE) IN (YEAR(CURRENT DATE),
                          YEAR(CURRENT DATE + 1 YEAR),
                          YEAR(CURRENT DATE + 2 YEARS));

A single-item list is legal and means equality: COL IN (1) is COL = 1. That matters for code generators and for predicate-processing notes that treat a one-item IN like an equality (often a good matching predicate).

Host structures can feed a list in COBOL when each element is a valid host variable. IBM shows EMPNO IN (:elem1, :elem2, :elem3) from a structure. The list is still a fixed set of SQL expressions at prepare time—not a varying array you expand at runtime unless you build dynamic SQL or use a subquery against a table of keys.

List size

Do not paste thousands of literals into an IN list if you can load a work table and join or use a subquery. Db2 for z/OS has a documented maximum number of IN-list elements (32,767 in current manuals). Accelerator-only exceptions exist under strict conditions (application compatibility, QUERY_ACCEL_OPTIONS, constants only, and a version 7 accelerator). If those conditions are not met, a huge list can fail with SQLCODE -101. Even below the limit, giant lists hurt parse time, statement cache, and readability.

IN with a subquery

The subquery form is “is this value equal to any value this query returns?”

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE EMPNO IN (SELECT EMPNO
                   FROM DSN8C10.EMP
                  WHERE WORKDEPT = 'E11');

IBM’s wording: the predicate is true for any row whose employee works in department E11 (here the subquery returns employee numbers of that department). The fullselect must return a single result column. It may return many rows, including nulls.

Uncorrelated subqueries (no outer-column reference) are often transformed to a join. Correlated IN subqueries re-evaluate per outer row and can be slower; they are still correct when written carefully. For “does a matching child exist?” many experienced shops prefer EXISTS because it stops at the first match and does not have the NOT IN null problem.

Quantified equivalents

IBM publishes a direct mapping. Learn it once; it explains NOT IN’s null behavior.

= ANY and = SOME are the same. <> ALL means “not equal to every member of the set.” If any member is null, “not equal to every member” cannot be TRUE.

NOT IN

NOT IN is the negated membership test. For a list of non-null constants it behaves as beginners expect:

1
2
3
SELECT EMPNO, WORKDEPT
  FROM DSN8C10.EMP
 WHERE WORKDEPT NOT IN ('A00', 'B01');

Rows with WORKDEPT A00 or B01 are excluded. Rows with other non-null departments are kept. Rows with null WORKDEPT are not kept: null NOT IN (…) is UNKNOWN.

You can also write NOT (WORKDEPT IN ('A00', 'B01')). For three-valued logic that is the same idea: NOT TRUE is FALSE, NOT FALSE is TRUE, NOT UNKNOWN is UNKNOWN.

The NULL pitfall with NOT IN subqueries

This is the most important IN lesson on z/OS. Suppose you write:

1
2
3
SELECT DEPTNO
  FROM DSN8C10.DEPT
 WHERE DEPTNO NOT IN (SELECT WORKDEPT FROM DSN8C10.EMP);

You wanted “departments that have no employees.” If any EMP.WORKDEPT is null, the subquery set contains null. Then for a department D99 that is not equal to any non-null WORKDEPT:

• D99 <> 'A00' is TRUE, D99 <> 'B01' is TRUE, …
• D99 <> NULL is UNKNOWN
• <> ALL of a mix of TRUE and UNKNOWN is UNKNOWN
• WHERE does not keep UNKNOWN, so D99 vanishes

If some WORKDEPT equals D99, that comparison is FALSE and <> ALL is FALSE—also dropped. Net effect: once the subquery can return a null, NOT IN is almost never TRUE. Reports come back empty and people blame “Db2 bugs.”

Safe rewrites:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1) Exclude nulls from the set
SELECT DEPTNO
  FROM DSN8C10.DEPT
 WHERE DEPTNO NOT IN (SELECT WORKDEPT
                        FROM DSN8C10.EMP
                       WHERE WORKDEPT IS NOT NULL);
 
-- 2) Prefer NOT EXISTS (null-safe for this question)
SELECT D.DEPTNO
  FROM DSN8C10.DEPT D
 WHERE NOT EXISTS (SELECT 1
                     FROM DSN8C10.EMP E
                    WHERE E.WORKDEPT = D.DEPTNO);
 
-- 3) Anti-join pattern
SELECT D.DEPTNO
  FROM DSN8C10.DEPT D
  LEFT JOIN DSN8C10.EMP E
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.EMPNO IS NULL;

IN itself is less poisonous: if the list contains null and a matching non-null value, IN can still be TRUE from the match. If there is no match, IN is UNKNOWN because of the null, so WHERE still drops the row. The disaster story is almost always NOT IN.

Row-value IN

When a row-value-expression is specified, IN compares that row with rows of a fullselect that has the same number of columns. Corresponding types must be compatible. IN is TRUE if at least one returned row equals the row-value-expression, FALSE if the subquery is empty or no row equals, and UNKNOWN if nulls make every remaining comparison unknown and no row fully matches.

1
2
3
4
5
SELECT EMPNO
  FROM DSN8C10.EMP
 WHERE (WORKDEPT, JOB) IN (SELECT DEPTNO, 'MANAGER'
                             FROM DSN8C10.DEPT
                            WHERE LOCATION = 'DALLAS');

Use this when the membership test is composite (department and job, account and currency). For NOT IN on rows, the same null rules apply across any null in the compared row or in a subquery row.

Performance notes

• IN-list of simple literals on an indexed column — often indexable / stage 1 when the list items are constants, host variables, or special registers without functions wrapping the column.
• NOT IN (list) — typically weaker (stage 1 not indexable in the usual predicate tables).
• NOT IN (noncorrelated subquery) — often stage 2. Rewrite to NOT EXISTS or an anti-join when it matters.
• Expressions on the column — YEAR(COL) IN (…) can prevent a matching index on COL. Prefer a range on the raw column when you can.

For a handful of codes, IN ('A','B','C') is clearer than three ORs and usually just as efficient. For a set that lives in a table, join or EXISTS. For a set that arrives from a file, INSERT into a declared global temporary table and join—do not build a 2,000-literal IN string in COBOL.

Explain It Like I'm Five

IN is checking whether your crayon is in the cup of allowed colors: red, blue, or green. NOT IN is checking that it is not in the cup. If someone dropped a crayon with the wrapper torn off so nobody can see the color (NULL) into the cup, “is my crayon not in this cup?” becomes “I can’t tell,” and the teacher who only keeps clear yes answers (WHERE) will not keep you. Empty the mystery crayon out of the cup (IS NOT NULL) or ask a different question: “is there no matching crayon on the table?” (NOT EXISTS).

Exercises

1. Write an IN list that keeps employees in D11, D21, and E11.
2. Rewrite that IN list as OR equalities, then as = ANY with a VALUES fullselect.
3. Explain why DEPTNO NOT IN (SELECT MGRNO FROM DEPT) is dangerous if MGRNO can be null.
4. Rewrite the previous predicate with NOT EXISTS so null managers do not hide departments.
5. Write a row-value IN that matches (WORKDEPT, JOB) to a two-column subquery.

Frequently asked questions

Is IN ('A', 'A') different from IN ('A')?

Logically both are “equals A.” Duplicate list items do not change TRUE/FALSE. They can still bloat the statement text. Distinct values only.

Does IN ignore trailing blanks?

Character comparison follows Db2 string comparison rules (padding, CCSID, mixed data), not a special IN rule. CHAR versus VARCHAR and pad blanks can make two values that “look” equal in a report compare unequal—or the reverse. Know your column types.

Can I use IN in the ON clause of a join?

Yes. ON is a search condition. IN in ON is a join filter, not a WHERE filter. That is useful on outer joins when you want to restrict which optional rows attach without dropping preserved rows.