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_checkcontains character data,NVLcasts its return value toVARCHAR.if
expression_to_checkis numeric,NVLcasts its return value to the data type of the expression (expression_to_checkorexpression_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 |
+------------+----------+