IFNULL()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns an alternate value in place of NULL.
USAGE
IFNULL(check_value, when_null_value)
Argument Name | Description |
|---|---|
| The value to compare to |
| The value that is returned when |
DETAILS
IFNULL() is a flow control function that returns either check_value or an alternate value when check_value is NULL.
The when_null_value expression can be NULL, though that makes the function call have no effect.
The return value's data type is based on the data type of the value being checked:
If the
when_null_valuevalue is a string or theNULLvalue's field type is a string (or a type that evaluates to a string), the return value is a string (even when returning thecheckvalue).
EXAMPLES
With Literal Values
In the following examples, IFNULL() is called with literal values:
SELECT IFNULL(10, 'ick') AS string_result;
+---------------+
| string_result |
+---------------+
| 10 |
+---------------+
SELECT IFNULL(CAST(NULL AS char), 1) AS string_result;
+---------------+
| string_result |
+---------------+
| 1 |
+---------------+
SELECT IFNULL(10/0, 2) AS numeric_result;
+----------------+
| numeric_result |
+----------------+
| 2 |
+----------------+
SELECT IFNULL(NULL, NULL) AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
Example Schema and Data
All of the examples are based on the example table contacts:
CREATE TABLE contacts (
first_name VARCHAR(25),
last_name VARCHAR(25),
email VARCHAR(25),
new_contact boolean
);
INSERT INTO contacts VALUES
('John', 'Smith', 'John.Smith@example.com', true),
('Jane', NULL, NULL, true),
('Ralph', NULL, 'Ralph@example.com', false),
(NULL, NULL, NULL, NULL);
Per-row Results
In the following example, IFNULL() is used to turn NULL into the string [MISSING]:
SELECT IFNULL(first_name, '[MISSING]') AS first_name,
IFNULL(last_name, '[MISSING]') AS last_name,
IFNULL(email, '[MISSING]') AS email
FROM contacts;
+------------+-----------+------------------------+
| first_name | last_name | email |
+------------+-----------+------------------------+
| John | Smith | John.Smith@example.com |
| Jane | [MISSING] | [MISSING] |
| Ralph | [MISSING] | Ralph@example.com |
| [MISSING] | [MISSING] | [MISSING] |
+------------+-----------+------------------------+
With GROUP BY
Using IFNULL() with GROUP BY creates a separate result for each group. In this example, GROUP BY last_name creates a separate result for each unique name:
SELECT last_name,
IFNULL(last_name,
CONCAT('[', COUNT(*), ']')) AS name_or_count
FROM contacts
GROUP BY last_name
ORDER BY last_name;
+-----------+---------------+
| last_name | name_or_count |
+-----------+---------------+
| NULL | [3] |
| Smith | Smith |
+-----------+---------------+