IS [NOT] DISTINCT FROM

Available in: DSQL, PSQL

Added in: 2.0

Description: Two operands are considered DISTINCT if they have a different value or if one of them is NULL and the other isn't. They are NOT DISTINCT if they have the same value or if both of them are NULL.

Result type: Boolean

Syntax:

op1 IS [NOT] DISTINCT FROM op2

Examples:

select id, name, teacher from courses
  where start_day is not distinct from end_day

if (New.Job is distinct from Old.Job)
  then post_event 'job_changed';

IS [NOT] DISTINCT FROM always returns true or false, never NULL (unknown). The “=” and “<>” operators, by contrast, return NULL if one or both operands are NULL. See also the table below.

Table 10.1. Comparison of [NOT] DISTINCT to “=” and “<>”

Operand characteristics	Results with the different operators
Operand characteristics	=	NOT DISTINCT	<>	DISTINCT
Same value	`true`	`true`	`false`	`false`
Different values	`false`	`false`	`true`	`true`
Both `NULL`	`NULL`	`true`	`NULL`	`false`
One `NULL`	`NULL`	`false`	`NULL`	`true`