DISTINCT predicate

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>---expression--IS--+-----+--DISTINCT FROM--expression--------><
                     '-NOT-'
 

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.

Example

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