IS NOT NULL

All functions > COMPARISON > IS NOT NULL

Returns TRUE if the expression does NOT evaluate to NULL.

Syntax

expr IS NOT NULL [:: type]

expr IS NOT NULL [:: type]

Notes

• Opposite of IS NULL
• Only reliable way to test for non-NULL values
• Using != NULL always returns NULL, not TRUE/FALSE
• Works with any data type; optional ::T after the keywords disambiguates the expression when its type would otherwise be unclear (for example EMPTY from an unmatched RELATED); when ::T is present it must match the expression type
• Never returns NULL itself - always Returns TRUE or FALSE

Related Functions

• IS_NOT_NULL()

Examples

IS NOT NULL — detection

SELECT
    f1 := 'x' IS NOT NULL::VARCHAR, -- Non-NULL string
    f2 := NULL::VARCHAR IS NOT NULL::VARCHAR -- NULL is not non-NULL
;

IS NOT NULL — composite values

SELECT
    f1 := ARRAY(1, 2) IS NOT NULL::ARRAY(BIGINT) -- Non-NULL array
;

IS NOT NULL — contrast with !=

SELECT
    f1 := NULL::BIGINT != NULL::BIGINT -- `!=` does not behave like IS NOT NULL
;