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()
Note: A
NULLvalue 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;
