meta data for this page
  •  

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 Results with the different operators Results with the different operators Results with the different operators Results with the different operators
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