NOT DISPLAYS AS

All functions > COMPARISON > NOT DISPLAYS AS

Returns TRUE if two values are distinct, treating NULL values as comparable.

Syntax

expr1 NOT DISPLAYS AS expr2  ·  expr1 IS DISTINCT FROM expr2

expr1 NOT DISPLAYS AS expr2  ·  expr1 IS DISTINCT FROM expr2

Notes

• Unlike !=, NULL is treated as comparable here (three-valued logic does not apply the same way)
• NULL NOT DISPLAYS AS NULL is FALSE (they are not distinct)
• value NOT DISPLAYS AS NULL is TRUE when value is not NULL
• NULL NOT DISPLAYS AS value is TRUE when value is not NULL
• Both values must be of the same type (including the same ARRAY/ROW shape and field/element types)
• Permissive spelling. Canonical null-safe inequality is IS DISTINCT FROM; FORMAT (FIX) rewrites this operator there

Related Functions

• NOT_DISPLAYS_AS()

See also

• Operators

• DISPLAYS_AS

• IS_DISTINCT_FROM

Examples

NOT DISPLAYS AS

RETURN
    -- Same values
    f1 := 1 IS DISTINCT FROM 1,
    -- Different values
    f2 := 1 IS DISTINCT FROM 2
;

NULL handling

RETURN
    -- Both NULL
    f1 := NULL::BIGINT IS DISTINCT FROM NULL::BIGINT,
    -- NULL vs non-NULL
    f2 := NULL::BIGINT IS DISTINCT FROM 1,
    -- Non-NULL vs NULL
    f3 := 1 IS DISTINCT FROM NULL::BIGINT
;

Other types

RETURN
    -- Same doubles
    f1 := 1e0 IS DISTINCT FROM 1e0,
    -- Both NULL doubles
    f2 := NULL::DOUBLE IS DISTINCT FROM NULL::DOUBLE
;

NOT DISPLAYS AS — arrays and rows

RETURN
    -- Distinct arrays
    f1 := ARRAY(1, 2) IS DISTINCT FROM ARRAY(1, 3),
    -- Both NULL arrays
    f2 := NULL::ARRAY<BIGINT> IS DISTINCT FROM NULL::ARRAY<BIGINT>
;