You are viewing an older version of this section. View current production version.
Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

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