IS DISTINCT FROM

All functions > COMPARISON > IS DISTINCT FROM

Returns TRUE if two values are distinct, treating NULL values as comparable (SQL-standard spelling).

Syntax

expr1 IS DISTINCT FROM expr2  ·  expr1 NOT DISPLAYS AS expr2

expr1 IS DISTINCT FROM expr2  ·  expr1 NOT DISPLAYS AS expr2

Notes

• Semantics match NOT DISPLAYS AS; this entry exists so FORMAT preserves IS DISTINCT FROM when you wrote it that way
• Unlike !=, NULL is treated as comparable here
• NULL IS DISTINCT FROM NULL is FALSE
• Operand types must match structurally (including ARRAY/ROW shape and field/element types)

Related Functions

• IS_DISTINCT_FROM()

See also

• Operators

• NOT_DISPLAYS_AS

Examples

IS DISTINCT FROM

SELECT
    f1 := (1 IS DISTINCT FROM 1), -- Same values
    f2 := (1 IS DISTINCT FROM 2), -- Different values
    f3 := (NULL::BIGINT IS DISTINCT FROM NULL::BIGINT), -- Both NULL
    f4 := (NULL::BIGINT IS DISTINCT FROM 1), -- NULL vs non-NULL
    f5 := (1 IS DISTINCT FROM NULL::BIGINT) -- Non-NULL vs NULL
;

IS DISTINCT FROM — other types

SELECT
    f1 := (1E0 IS DISTINCT FROM 1E0), -- Same doubles
    f2 := (NULL::DOUBLE IS DISTINCT FROM NULL::DOUBLE) -- Both NULL doubles
;

IS DISTINCT FROM — arrays and rows

SELECT
    f1 := (ARRAY(1, 2) IS DISTINCT FROM ARRAY(1, 3)) -- Distinct arrays
;