Loading lesson path
SQL COALESCE(), IFNULL(), ISNULL(), and NVL() Functions Operations involving NULL values can sometimes lead to unexpected results.
Formula
SQL has some built - in functions to handle NULL values, and the most common functions are: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.
Assume we have the following "Products" table:
10.45 16 15
32.56 23 null
15.67
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!
The COALESCE() function is the preferred standard for handling potential NULL values.
Formula
The COALESCE() function returns the first non - NULL value in a list of values.MySQL, SQL Server, and Oracle (not in MS Access).
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;
The IFNULL() Function (MySQL)IFNULL() function replaces NULL with a specified value.
expr, alt ) Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + IFNULL(InOrder, 0))
FROM Products;
The ISNULL() Function (SQL Server)