The DISTINCT predicate compares a value with another value.
When the predicate is IS DISTINCT, the result of the predicate is true if the comparison of the expressions evaluates to true. Otherwise, the result of the predicate is false. The result cannot be unknown.
When the predicate IS NOT DISTINCT FROM, the result of the predicate is true if the comparison of the expressions evaluates to true (null values are considered equal to null values). Otherwise, the result of the predicate is false. The result cannot be unknown.
The DISTINCT predicate:
value1 IS NOT DISTINCT FROM value2
is logically equivalent to the search condition:
( value1 IS NOT NULL AND value2 IS NOT NULL AND value1 = value2 ) OR ( value1 IS NULL AND value2 IS NULL )
The DISTINCT predicate:
value1 IS DISTINCT FROM value2
is logically equivalent to the search condition:
NOT (value1 IS NOT DISTINCT FROM value2)
If the operands of the DISTINCT predicate are strings with different CCSIDs, operands are converted as if the above logically-equivalent search conditions were specified.
If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the collating sequence.
Assume that table T1 exists and it has a single column C1, and three rows with the following values for C1: 1, 2, null. The following query produces the following results:
SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV
| C1 | :HV | Result |
|---|---|---|
| 1 | 2 | True |
| 2 | 2 | False |
| 1 | Null | True |
| Null | Null | False |
The following query produces the following results:
SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV
| C1 | :HV | Result |
|---|---|---|
| 1 | 2 | False |
| 2 | 2 | True |
| 1 | Null | False |
| Null | Null | True |