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)
• SQL spelling IS DISTINCT FROM is a separate operator/function pair (same semantics, preserves formatting)

Related Functions

• NOT_DISPLAYS_AS()

See also

• Operators

• DISPLAYS_AS

• IS_DISTINCT_FROM

Examples

NOT DISPLAYS AS

SELECT
    -- Same values
    f1 := 1 NOT DISPLAYS AS 1,
    -- Different values
    f2 := 1 NOT DISPLAYS AS 2
;

NULL handling

SELECT
    -- Both NULL
    f1 := NULL::BIGINT NOT DISPLAYS AS NULL::BIGINT,
    -- NULL vs non-NULL
    f2 := NULL::BIGINT NOT DISPLAYS AS 1,
    -- Non-NULL vs NULL
    f3 := 1 NOT DISPLAYS AS NULL::BIGINT
;

Other types

SELECT
    -- Same doubles
    f1 := 1e0 NOT DISPLAYS AS 1e0,
    -- Both NULL doubles
    f2 := NULL::DOUBLE NOT DISPLAYS AS NULL::DOUBLE
;

NOT DISPLAYS AS — arrays and rows

SELECT
    -- Distinct arrays
    f1 := ARRAY(1, 2) NOT DISPLAYS AS ARRAY(1, 3),
    -- Both NULL arrays
    f2 := NULL::ARRAY(BIGINT) NOT DISPLAYS AS NULL::ARRAY(BIGINT)
;