DISTINCT predicate

A distinct predicate compares a value with another value or a set of values with another set of values.

expression and row-value-expression cannot be array expressions.

The number of elements that are returned by the row-value-expression that specified after the distinct operator must match the number of elements that are returned by the row-value-expression that is specified prior to the distinct operator. The data types of the corresponding columns or expressions of the row-value-expressions must be compatible. When the predicate is evaluated, the value of each expression on the left side is compared with the value of its corresponding expression on the right side. The result of the predicate depends on the form of the predicate.

When the predicate is IS DISTINCT, the result of the predicate is true if at least one comparison of a pair of corresponding value expressions evaluates to false. 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 all pairs of corresponding value expressions evaluate to true (null values are considered equal to null values). Otherwise, the predicate is false. The result cannot be unknown.

The DISTINCT predicate cannot be used in the following contexts:

The ON join-condition of a full outer join
A check constraint
A quantified predicate

The following DISTINCT predicates are logically equivalent to the corresponding search conditions:

DISTINCT predicate Search condition

Table 1. DISTINCT predicates and logically equivalent search conditions
DISTINCT predicate	Search condition
`value 1 IS NOT DISTINCT FROM value2`	`(value1 IS NOT NULL AND value2 IS NOT NULL AND value1 = value 2) OR (value1 IS NULL AND value2 IS NULL)`
`value 1 IS DISTINCT FROM value2`	`NOT (value1 IS NOT DISTINCT FROM value2)`

   value 1 IS NOT DISTINCT FROM value2

(value1 IS NOT NULL 
 AND value2 IS NOT NULL 
 AND value1 = value 2)
OR
(value1 IS NULL 
 AND value2 IS NULL)

value 1 IS DISTINCT FROM value2

NOT (value1 IS NOT DISTINCT FROM value2)

Example 1: Assume that T1 is a single-column table with three rows. Column C1 has the following values: 1, 2, and null. Consider the following query:

  SELECT * FROM T1
     WHERE C1 IS DISTINCT FROM :HV;

The following table shows the value of the predicate for various values of C1 and the host variable.

Value of C1	Value of `HV`	Result of predicate
1	2	True
2	2	False
null	2	True
1	null	True
2	null	True
null	null	False

Example 2: Assume the same table as in the first example, but now consider the negative form of the predicate in the query:

  SELECT * FROM T1
     WHERE C1 IS NOT DISTINCT FROM :HV;

The following table shows the value of the predicate for various values of C1 and the host variable.

Value of C1	Value of `HV`	Result of predicate
1	2	False
2	2	True
null	2	False
1	null	False
2	null	False
null	null	True