NVL2

Return one value if expr is not NULL, another if it is NULL.

Category: conditionalReturns: ANYDialect: Standard

Syntax

NVL2(expr, not_null_value, null_value)

Description

## Overview Evaluates expr and returns not_null_value when expr is not NULL, or null_value when expr is NULL. Unlike NVL, which returns expr or a fallback, NVL2 supports distinct return values for both the NULL and non-NULL cases. NVL2 is equivalent to CASE WHEN expr IS NOT NULL THEN not_null_value ELSE null_value END. Use it when you want a presence indicator or a two-way branch keyed only on nullity. ## Behavior - Returns not_null_value when expr IS NOT NULL (regardless of the actual value). - Returns null_value when expr IS NULL. - Short-circuits: only one of the branch arguments is evaluated. - Result type is the common supertype of not_null_value and null_value. - Empty string is not NULL; NVL2('', a, b) returns a. ## Compatibility - NVL2 originated in Oracle SQL and is supported by most SQL engines with identical semantics. - Equivalent to CASE WHEN expr IS NOT NULL THEN not_null_value ELSE null_value END.

Parameters

Name	Type	Description
`expr`		Specifies the expression tested for NULL. Its value is not returned directly; it only controls which branch is chosen.
`not_null_value`		Specifies the value returned when expr is not NULL.
`null_value`		Specifies the value returned when expr is NULL. Must be coercible to a common supertype with not_null_value.

Examples

-- Non-NULL expr selects the second argument
SELECT NVL2('data', 'has value', 'is null') AS v;  -- 'has value'

-- NULL expr selects the third argument
SELECT NVL2(NULL, 'has value', 'is null') AS v;  -- 'is null'

-- Realistic: classify users by presence of an email
SELECT user_id, NVL2(email, 'contactable', 'no email') AS status
FROM iam.directory.users;

-- Numeric branching based on presence of a discount
SELECT order_id,
       NVL2(discount, price * (1 - discount), price) AS final_price
FROM ecommerce.sales.orders;

-- Combine with aggregation for coverage reporting
SELECT COUNT(*) AS total,
       SUM(NVL2(email, 1, 0)) AS with_email,
       SUM(NVL2(email, 0, 1)) AS without_email
FROM iam.directory.users;

Pitfalls

not_null_value and null_value must share a common supertype; mixing STRING and numeric raises a type error.
expr's value is ignored; only its nullity matters. Callers sometimes expect NVL2(col, default) semantics (which would be NVL).
Empty strings are not NULL; NVL2('', a, b) returns a, not b. Use LENGTH or NULLIF to treat empty strings as missing.
Short-circuit evaluation is not guaranteed; side effects in either branch may not fire in every engine.
The return type widens to the common supertype, which can surprise integer-only callers when mixing INT and DECIMAL.