Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.


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.


NVL ( expression_to_check, expression_if_null )



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.


An expression that evaluates to a value of any data type. NVL returns this value if expression_to_check is NULL.


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.


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  |