ISNOTNULL

Return TRUE if the expression is not NULL, otherwise FALSE.

Category: conditionalReturns: BOOLEANDialect: Standard

Syntax

ISNOTNULL(expr)

Description

## Overview Returns TRUE if the expression is not NULL and FALSE otherwise. ISNOTNULL is the functional form of the IS NOT NULL predicate and can be used in any boolean expression context. ## Behavior - Returns FALSE only when the argument is NULL. - Returns TRUE for all non-NULL values, including 0, '', false, and empty arrays. - Never returns NULL. - Accepts any scalar, composite, or array type. - Equivalent to (expr IS NOT NULL). ## Compatibility - Equivalent to the SQL-standard IS NOT NULL predicate. Prefer IS NOT NULL for portability; use ISNOTNULL when the codebase convention is functional-form null tests.

Parameters

Name	Type	Description
`expr`		Specifies the expression to test for non-nullity. Accepts any data type.

Examples

-- Non-NULL returns TRUE
SELECT ISNOTNULL(42) AS v;  -- true

-- NULL returns FALSE
SELECT ISNOTNULL(NULL) AS v;  -- false

-- Empty string is not NULL
SELECT ISNOTNULL('') AS v;  -- true

-- Filter rows with a non-NULL value
SELECT val
FROM (VALUES (1), (NULL), (3)) AS t(val)
WHERE ISNOTNULL(val);

-- Realistic: compute coverage on a column
SELECT COUNT(*) AS total,
       SUM(CASE WHEN ISNOTNULL(email) THEN 1 ELSE 0 END) AS with_email
FROM iam.directory.users;

Pitfalls

ISNOTNULL does not treat empty strings, zero, or false as missing. Use a compound predicate such as (ISNOTNULL(s) AND s <> '') when you need to exclude empty strings.
For portable SQL prefer the IS NOT NULL predicate. Some dialects do not expose ISNOTNULL as a function.
ISNOTNULL never returns NULL. Do not wrap in COALESCE to handle a 'NULL' result.
On indexed columns the optimizer should treat ISNOTNULL(col) and col IS NOT NULL identically; verify with an EXPLAIN plan if in doubt.
Writing NOT ISNULL(col) is equivalent but harder to read than ISNOTNULL(col) or col IS NOT NULL.