There are a lot of options to use when testing for NULL values in SQL. One should use built-in functions like Coalesce, NVL, NVL2 and ISNULL, or build its own expressions using the CASE statement. Let’s see how some of those alternatives work.
Why to use SQL NULL functions and statements?
Coalesce, NVL, ISNULL and the Case statement can be used in the Where clause of SQL statements to filter data or can be used in Select simply to format output results (mostly in Outer Join statements).
Consider the Products table in the following figure. Columns Discount and OnOrder are optional and may cointain NULL values, as represented in the figure.
Now suppose one wants to apply the following formula to calculate a value for each product: (Price – Discount) * (Stock + OnOrder). If we use such formula in SQL without dealing with NULL values, then the result may not be exactly the one we desire, as represented in the following.
To remove the NULL from TOTALVALUE for shoes and t-shirts, we can use SQL NULL functions or the Case statement.
Coalesce
The Coalesce function has been part of the SQL standard for several years and we can use it in most of widely used DBMS (like Oracle, SQL Server, PostgreSQL and MySQL). Coalesce accepts an unlimited number of arguments and returns the first argument that is not NULL. It returns NULL If all supplied arguments are NULL.
Coalesce can be used to solve the TotalValue calculation, as in the following.
Oracle’s NVL and NVL2
NVL is an Oracle-specific function that accepts only two parameters (while Coalesce accepts an unlimitted number of them): if the first parameter is NULL, it returns the second one. NVL2 is also an Oracle-specific function and accepts three parameters: if the first one is not null, then it returns the second one, otherwise NVL2 returns the value of third parameter.
Both NVL and NVL2 can be used to evaluate the TotalValue from Products. In the following we present how to use NVL2 (as NVL is used the same way as Coalesce).
ISNULL
The ISNULL function exists in several relational DBMS, including Microsoft SQL Server, MySQL and in Microsoft Access. But it works in distinct ways in such database management systems.
In Microsoft SQL Server, ISNULL accepts two paramenters: if the first one is NULL, then the function returns the second one. It can be used to solve the TotalValue calculation just like Coalesce and NVL, but it would only work in SQL Server.
In MySQL, ISNULL accepts one parameter and returns 1 if such parameter is NULL or 0 if it is not null. The MS Access ISNULL is similar to the one of MySQL, as it accepts only one parameter. But the ISNULL function of MS Access would return True if the parameter is null or False if it is not null. If one wants to calculate TotalValue in MySQL or in MSAccess using ISNULL, then he/she would need to use ISNULL togheter with the CASE statement.
CASE Statement
The CASE statement can also be used to test for NULL values, but most of the times would lead to a more complex statement (or it at least for a longer SQL statement). In the following, we use CASE to evaluate the TotalValue of each product.
We can also use CASE statement together with ISNULL in MySQL and in Access to evaluate TotalValue.
What should I use?
The Coalesce function is SQL-standard and can be used in the same way in many DBMS. Also, Coalesce is more versatile than Oracle’s NVL and SQL Server’s ISNULL, as it is capable to test for NULL values in several parameters and not only in two. Using Coalesce would also lead to a smaller (and many times more clear) SQL sentence than the one obtained when using the CASE statement. That’s why its the recommend alternative to use.
EXCELLENT, VERY GOOD…