NVL and IFNULL

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.

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