DISTINCT predicate

The DISTINCT predicate compares two expressions and evaluates to TRUE if their values are not identical.

The result of a DISTINCT predicate depends on whether either or both of its input expressions are null:
Input expressions IS DISTINCT FROM IS NOT DISTINCT FROM
Both inputs are non-null. Evaluates to TRUE if the inputs are not identical and FALSE if they are. Equivalent to the <> operator. Evaluates to FALSE if the inputs are not identical and TRUE if they are. Equivalent to the = operator.
One input is null. Evaluates to TRUE. Evaluates to FALSE.
Both inputs are null. Evaluates to FALSE. Evaluates to TRUE.
The result of a DISTINCT predicate cannot be null.
Read syntax diagramSkip visual syntax diagram expression1 IS NOT DISTINCT FROM expression2
expression1 and expression2
The expressions that are to be compared.

Examples

Assume that HV is a host variable and T1 is a table with one column (C1) and three rows:
C1
----
1
2
NULL
  • If HV=2, the statement
    SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV;
    returns rows 1 and 3.
    If HV=2, the statement
    SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV;
    returns row 2.
  • If HV=NULL, the statement
    SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV;
    returns rows 1 and 2.
    If HV=NULL, the statement
    SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV;
    returns row 3.