IFNULL

Return expr1 if not NULL, otherwise expr2.

Category: conditionalReturns: ANYDialect: Standard

Syntax

IFNULL(expr1, expr2)

Description

## Overview Returns expr1 if it is not NULL, otherwise returns expr2. IFNULL is an alias for NVL and is functionally equivalent to COALESCE with exactly two arguments. Use IFNULL for simple two-way NULL substitution when that name is the convention in your codebase. ## Behavior - Short-circuits: when expr1 is not NULL, expr2 is not evaluated. - Returns NULL if both arguments are NULL. - Result type is the common supertype of expr1 and expr2. - Does not treat empty strings as NULL. ## Compatibility - IFNULL is widely available across SQL dialects with identical semantics. - Equivalent to NVL(expr1, expr2) and COALESCE(expr1, expr2).

Parameters

Name	Type	Description
`expr1`		Specifies the expression to test for NULL. If this value is not NULL, it is returned as the result.
`expr2`		Specifies the fallback value returned when expr1 is NULL. Must be coercible to a common supertype with expr1.

Examples

-- Non-NULL first argument is returned
SELECT IFNULL('hello', 'default') AS v;  -- 'hello'

-- NULL first argument returns the fallback
SELECT IFNULL(NULL, 'default') AS v;  -- 'default'

-- Both NULL returns NULL
SELECT IFNULL(NULL, NULL) AS v;  -- NULL

-- Realistic: default email when missing
SELECT user_id, IFNULL(email, 'no-email@example.com') AS email
FROM iam.directory.users;

-- Default numeric for aggregation
SELECT product_id, SUM(IFNULL(quantity, 0)) AS total_qty
FROM ecommerce.sales.order_lines
GROUP BY product_id;

Pitfalls

Arguments must share a common supertype; mixing STRING and numeric raises a type error.
Empty strings are not NULL; use NULLIF(column, '') first if you need to substitute for empty strings.
IFNULL, NVL, and COALESCE(a, b) all produce the same result. Pick one convention per codebase.
Result type may widen to the common supertype, changing numeric precision in surprising ways.
Short-circuit evaluation is not guaranteed by the standard; do not rely on expr2 being skipped for side effects.