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
    f1 := 1 NOT DISPLAYS AS 1, -- Same values
    f2 := 1 NOT DISPLAYS AS 2 -- Different values
;

NULL handling

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

Other types

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

NOT DISPLAYS AS — arrays and rows

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