ISNULL

Return TRUE if the expression is NULL, otherwise FALSE.

Category: conditionalReturns: BOOLEANDialect: Standard

Syntax

ISNULL(expr)

Description

## Overview Returns TRUE if the expression is NULL and FALSE otherwise. ISNULL is the functional form of the IS NULL predicate and can be used anywhere a boolean expression is accepted: SELECT projections, CASE branches, scalar subqueries, and aggregate arguments. Note the distinction from IFNULL: ISNULL tests nullity and returns BOOLEAN, while IFNULL substitutes a value when the input is NULL. ## Behavior - Returns TRUE only when the argument is NULL. - Returns FALSE 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 NULL) as a predicate. ## Compatibility - ISNULL is available across most SQL dialects but some engines use IS NULL only (without a functional form) and repurpose ISNULL as a two-argument alias for IFNULL. Check the target dialect when porting. - DeltaForge's ISNULL is the one-argument nullity test.

Parameters

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

Examples

-- NULL returns TRUE
SELECT ISNULL(NULL) AS v;  -- true

-- Non-NULL returns FALSE
SELECT ISNULL(42) AS v;  -- false

-- Zero is not NULL
SELECT ISNULL(0) AS v;  -- false

-- Use in a SELECT projection
SELECT val, ISNULL(val) AS is_missing
FROM (VALUES (1), (NULL), (3)) AS t(val);

-- Realistic: count missing emails per department
SELECT department,
       SUM(CASE WHEN ISNULL(email) THEN 1 ELSE 0 END) AS missing_email,
       COUNT(*) AS total
FROM hr.core.employees
GROUP BY department;

Pitfalls

Do not confuse ISNULL (boolean nullity test) with IFNULL (value substitution). In some engines ISNULL is a two-argument substitution; in DeltaForge it is a one-argument boolean predicate.
ISNULL does not treat empty strings, zero, or false as NULL. Use combined conditions such as (ISNULL(s) OR s = '') when empty-string normalization is needed.
For most engines the optimizer treats ISNULL(col) and col IS NULL identically; prefer the SQL-standard IS NULL predicate for portability.
ISNULL never returns NULL itself; do not wrap in COALESCE to handle 'NULL' results of this function.
On large tables, an index on the column still applies to ISNULL(col) in engines that handle both IS NULL and the functional form; verify by checking the query plan.