NVL and IFNULL

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.

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 to VARCHAR.
if expression_to_check is numeric, NVL casts its return value to the data type of the expression (expression_to_check or expression_if_null) that has the higher numeric precision.

Examples

The following example demonstrates the simplest case of calling NVL.

SQL

SELECT NVL(NULL, 'NULL value') AS 'result';

+------------+
| result     |
+------------+
| NULL value |
+------------+

The following example shows how to call NVL once per table row.

SQL

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  |
+------------+----------+