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.
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 |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.