NVL and IFNULL
Checks if an expression evaluates to NULL
, and if so, evaluates a replacement expression and returns its value.
The NVL
and IFNULL
functions are synonyms. The following discussion of NVL
applies to IFNULL
, as both work identically.
Syntax
NVL ( expression_to_check, expression_if_null )
Arguments
expression_to_check
An expression that evaluates to a value of any data type. NVL
checks if this value is NULL
, and if so, evaluates expression_if_null
and returns the result.
expression_if_null
An expression that evaluates to a value of any data type. NVL
returns this value if expression_to_check
is NULL
.
Remarks
If expression_to_check
and expression_if_null
have different data types, then NVL
casts its return value as follows:
If
expression_to_check
contains character data,NVL
casts its return value toVARCHAR
.if
expression_to_check
is numeric,NVL
casts its return value to the data type of the expression (expression_to_check
orexpression_if_null
) that has the higher numeric precision.
Examples
The following example demonstrates the simplest case of calling NVL
.
SELECT NVL(NULL, 'NULL value') AS 'result'; **** +------------+ | result | +------------+ | NULL value | +------------+
The following example shows how to call NVL
once per table row.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; CREATE TABLE person(name VARCHAR(80), state VARCHAR(40)); INSERT INTO person VALUES ('John Jones', 'New York'), ('Mary Smith', NULL), ('Ann Brown', NULL); SELECT name, NVL(state, 'Unknown') AS state FROM person ORDER BY name; **** +------------+----------+ | name | state | +------------+----------+ | Ann Brown | Unknown | | John Jones | New York | | Mary Smith | Unknown | +------------+----------+