MySQL NULL Functions
MySQL COALESCE() and IFNULL() Functions
Operations involving NULL values can sometimes lead to unexpected results.
MySQL has two built-in functions to handle NULL values:
COALESCE()IFNULL()
A NULL value represents an unknown or missing data in a database field. It is not a value itself, but a placeholder to indicate the absence of data.
Demo Database
Assume we have the following "Products" table:
| PId | ProductName | Price | InStock | InOrder |
|---|---|---|---|---|
| 1 | Jarlsberg | 10.45 | 16 | 15 |
| 2 | Mascarpone | 32.56 | 23 | null |
| 3 | Gorgonzola | 15.67 | 9 | 20 |
The "InOrder" column is optional, and may contain NULL values.
Now look at the following SQL statement:
SELECT ProductName, Price * (InStock + InOrder)
FROM Products;
Note: In the SQL above, if any of the "InOrder" values are NULL, the result will be NULL!
MySQL COALESCE() Function
The
COALESCE() function is the preferred standard for handling potential NULL values.
The COALESCE() function returns the first non-NULL value in a list
of values.
Syntax
COALESCE(val1, val2, ...., val_n)
Here we use the
COALESCE() function to replace NULL
values with 0:
SELECT ProductName, Price * (InStock + COALESCE(InOrder, 0))
FROM Products;MySQL IFNULL() Function
The IFNULL()
function
function replaces NULL with a specified value.
Syntax
IFNULL(expr, alt)
Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + IFNULL(InOrder, 0))
FROM Products;
