is distinct operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Tests whether the arguments have different values where NULLs are considered as comparable values.

Syntax 

expr1 is [not] distinct from expr2

Arguments

  • expr1: An expression of a comparable type.

  • expr2: An expression of a type sharing a least common type with expr1.

Returns

A BOOLEAN.

If both expr1 and expr2 are NULL they are considered not distinct.

If only one of expr1 and expr2 is NULL the expressions are considered distinct.

If both expr1 and expr2 are not NULL they are considered distinct if expr <> expr2.

Examples 

> SELECT NULL is distinct from NULL;
 false

> SELECT NULL is distinct from 5;
 true

> SELECT 1 is distinct from 5;
 true

> SELECT NULL is not distinct from 5;
 false