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:
The result of a DISTINCT predicate cannot be 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. |
- 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
returns rows 1 and 3.SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV;
If HV=2, the statement
returns row 2.SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV;
- If HV=NULL, the
statement
returns rows 1 and 2.SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV;
If HV=NULL, the statement
returns row 3.SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV;