Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
NVL and IFNULL
On this page
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.NVL
applies to IFNULL
, as both work identically.
Syntax
NVL ( expression_to_check, expression_if_null )
Arguments
expression_
An expression that evaluates to a value of any data type.NVL
checks if this value is NULL
, and if so, evaluates expression_
and returns the result.
expression_
An expression that evaluates to a value of any data type.NVL
returns this value if expression_
is NULL
.
Remarks
If expression_
and expression_
have different data types, then NVL
casts its return value as follows:
-
If
expression_
contains character data,to_ check NVL
casts its return value toVARCHAR
. -
if
expression_
is numeric,to_ check NVL
casts its return value to the data type of the expression (expression_
orto_ check expression_
) that has the higher numeric precision.if_ null
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 personORDER BY name;
+------------+----------+
| name | state |
+------------+----------+
| Ann Brown | Unknown |
| John Jones | New York |
| Mary Smith | Unknown |
+------------+----------+
Last modified: February 24, 2023