Functions that handle NULL values in databases
16th
December 2023, 13:19
Not every value in a database has a well-defined value. Sometimes there is no value, or a NULL.
In these cases, you may need to handle these values, especially if there are calculations involved. Take the following table, TABLE_SALES, for example. There are missing values in the DISCOUNT column.
TABLE_SALES
Now let's say we tried this query.
This does not present a problem.
But what if we wanted to use it as part of a calculation? You would have situations where we tried to add NULL values to the value of SUBTOTAL.
In all of these cases, two arguments are passed in. The first is the value that could be NULL. The second is the value to substitute it with if the value is NULL. Thus, for Oracle, it would be...
This is nice and neat, but we can do better. The problem here is portability. If you had to move your data from Oracle to MySQL, for example, you would have to change all instances of NVL() to ISNULL().
Well, you slip in any number of arguments to the COALSECE() function call, and the function will return the first non-NULL value. Thus...
.... will return this.
So if we did this...
...it would return this. And you would be able to use that same function anywhere!
Tags
See also
Empty values!
In these cases, you may need to handle these values, especially if there are calculations involved. Take the following table, TABLE_SALES, for example. There are missing values in the DISCOUNT column.
TABLE_SALES
| DATETIME | ITEM | QTY | SUBTOTAL | DISCOUNT |
| 2023-10-10 12:13:10 | STRAWBERRY WAFFLE | 2 | 20 | 0 |
| 2023-10-10 12:44:54 | STRAWBERRY WAFFLE | 1 | 10 | 0 |
| 2023-10-11 15:03:09 | CHOCO DELIGHT | 1 | 25 | -2.5 |
| 2023-10-11 18:22:42 | ORANGE SLICES | 5 | 30 | |
| 2023-10-12 10:56:01 | STRAWBERRY WAFFLE | 4 | 40 | -3 |
Now let's say we tried this query.
SELECT DISCOUNT FROM TABLE_SALES
This does not present a problem.
| DISCOUNT |
| 0 |
| 0 |
| -2.5 |
| -3 |
But what if we wanted to use it as part of a calculation? You would have situations where we tried to add NULL values to the value of SUBTOTAL.
SELECT (SUBTOTAL + DISCOUNT) AS NETT FROM TABLE_SALES
Functions to handle NULL values
There are functions to handle these cases. They are named differently in different database systems. In SQLServer, it's IFNULL(). In MySQL, it's ISNULL(). In Oracle, it's NVL().In all of these cases, two arguments are passed in. The first is the value that could be NULL. The second is the value to substitute it with if the value is NULL. Thus, for Oracle, it would be...
SELECT (SUBTOTAL + NVL(DISCOUNT, 0)) AS NETT FROM TABLE_SALES
This is nice and neat, but we can do better. The problem here is portability. If you had to move your data from Oracle to MySQL, for example, you would have to change all instances of NVL() to ISNULL().
The COALESCE() function
COALESCE() is a function that exists in all of the databases mentioned above. How does it work?Well, you slip in any number of arguments to the COALSECE() function call, and the function will return the first non-NULL value. Thus...
COALSECE(NULL, NULL, 0.5, 1, NULL, 8)
.... will return this.
0.5
So if we did this...
SELECT (SUBTOTAL + COALESCE(DISCOUNT, 0)) AS NETT FROM TABLE_SALES
...it would return this. And you would be able to use that same function anywhere!
| NETT |
| 20 |
| 10 |
| 23.5 |
| 30 |
| 37 |
Finally...
Handling NULL values is important. Whether you choose to handle them at the data entry level (not allowing NULL values in a column) or in a calculation (using the COALESCE() function), at some point you have to handle them. I hope this helped!NULL and forever,
