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 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';

Output:

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

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

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_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;

Output:

+------------+----------+
| name       | state    |
+------------+----------+
| Ann Brown  | Unknown  |
| John Jones | New York |
| Mary Smith | Unknown  |
+------------+----------+

Was this Article Helpful?

Thanks For Your Feedback!