IN

The IN operator tests whether a value matches any item in a list. It is shorthand for a chain of OR conditions on the same column.

IN with a value list

• Syntax: WHERE column IN (value1, value2, value3).
• The list can hold numbers, quoted strings, or dates - all of one compatible type.
• IN ('A00','C01','D11') is the same as = 'A00' OR = 'C01' OR = 'D11', but shorter.
• NOT IN reverses the test: the value must match none of the items.

IN with a subquery

• The list can come from another SELECT: WHERE EMPNO IN (SELECT MGRNO FROM EMP).
• The subquery must return exactly one column.
• Types must be compatible between the outer column and the subquery column.
• If the subquery returns no rows, IN is false for every row - NOT IN is unknown.

IN, OR and NULL

• IN is cleaner than a long OR chain and easier for DB2 to optimise.
• Duplicate values in the list are harmless - they change nothing.
• An empty list IN () is a syntax error; build the list carefully in dynamic SQL.
• NULL in a NOT IN list wipes out the whole result - the classic IN trap.

Example