Functions that handle NULL values in databases

Not every value in a database has a well-defined value. Sometimes there is no value, or a NULL.

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,
T___T