Return expr1 if not NULL, otherwise expr2.
NVL(expr1, expr2)## Overview Returns expr1 if it is not NULL, otherwise returns expr2. NVL is functionally equivalent to IFNULL and to COALESCE with exactly two arguments. The return type is the common supertype of both arguments. Use NVL for simple two-way NULL substitution; use COALESCE when you need three or more fallbacks. ## 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; '' is returned as '' unchanged. ## Compatibility - Originally popularized by Oracle's NVL; the identical two-argument pattern is available in most SQL engines. - Equivalent to COALESCE(expr1, expr2) and to CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END.
| 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. |
-- Non-NULL first argument is returned
SELECT NVL(42, 0) AS v; -- 42-- NULL first argument returns the fallback
SELECT NVL(NULL, 0) AS v; -- 0-- String fallback
SELECT NVL(NULL, 'unknown') AS v;-- Realistic: default for nullable columns
SELECT user_id, NVL(phone, 'N/A') AS phone
FROM iam.directory.users;-- Combined with arithmetic
SELECT order_id, NVL(discount_amount, 0) * 1.0 AS disc
FROM ecommerce.sales.orders;