IS [NOT] DISTINCT FROM

`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
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

Search

German

Follow IBExpertise on:

edit SideBar

the database experts

IS [NOT] DISTINCT FROM

IS [NOT] DISTINCT FROM

Search

`IS [NOT] DISTINCT FROM`