How To Handle NULLs In SQL

In the last post, I wrote about the chaos NULL can cause in SQL. But if we have NULL already in the db, we cannot avoid working with them.

Let’s collect some best practices for handling NULL in SQL:

IS NULL Condition

One mistake beginners think is that NULL is a value that we can compare to numbers or other data types. But regular comparison operators like “=”, “<>”, “<“, “>” are not working with NULL in SQL. A NULL cannot be compared to another NULL either. NULL = NULL is FALSE because NULL means “unknown,” so two unknown values cannot be compared.

Here is a great exercise to understand the NULL logic:

employees table contains the following values:

SELECT * FROM employees where department_id = NULL;

As mentioned above, = will not work with NULL, so the result will be an empty table (no rows returned).

If you are thinking with the ‘general’ binary programming logic then you would expect that running the two lines below will return all the rows:

SELECT * FROM employees WHERE department_id = 2;

SELECT * FROM employees WHERE department_id <> 2;

But that’s wrong. The results are these:

Poor Charlie is missing since NULL <> 2 is UNKNOWN.

To get all the values we need to add the following:

SELECT * FROM employees WHERE department_id IS NULL

The IS NULL condition is used to return rows that contain the NULL values in a column. Its variation IS NOT NULL will return all non NULL values.

ISNULL() is different!

ISNULL()function is different from IS NULL. Adding to the confusion different Database systems have different functionality assigned to them.

ISNULL(column, default_value) replaces NULL with a given value in SQL Server.

SELECT name, ISNULL(department_id, 'Not Assigned') AS department
FROM employees;

In MySQL ISNULL() is used to test whether an expression is NULL or not. It returns 1 if the expression is NULL, and 0 otherwise.

SELECT name, ISNULL(department_id) AS is_null
FROM employees;

In MySQL if we want to replace NULL we need to use the IFNULL() function. Confusing right?

IFNULL(column, default_value) replaces NULL values with a specified default in MySQL.

NULLIF()

NULLIF(expr1, expr2) returns NULL if two expressions are equal, otherwise it returns the first expression. It is basically the same as:

CASE WHEN (expr1 = expr2)
THEN NULL
ELSE V1 END

COALESCE()

And finally, the one I use the most. You can pass many parameters into COALESCE() and it will return the first non NULL value. It checks all the values from left to right. This can replace most of the functions mentioned above.

Let’s look at some examples:

SELECT name, COALESCE(phone_number, 'N/A') AS phone_number
FROM customers;

If phone_number is NULL, it will be replaced with 'N/A'

We can add more parameters to it:

SELECT employee_id, name, COALESCE(home_phone, work_phone, mobile_phone, 'No phone available') AS contact_number
FROM employees;

For John the first non NULL value going from left to right is work_phone, but for Mark since all are NULL we replace them with a default value.

You can also use it in calculations. NULL will impact all calculations. If you include a NULL, the result will be NULL as well.

If a column might have NULL values in a calculation, you can replace them with a default:

SELECT order_id, price, discount, COALESCE(price - discount, price) AS final_price
FROM orders;

If discount is NULL, price - discount would result in NULL.

COALESCE(price - discount, price) ensures that if discount is NULL, the original price is used.

Without COALESCE() we would be screwed.

SELECT order_id, price, discount, 
       price - discount AS final_price
FROM orders;

If you are running a webshop you probably don’t want to show NULL as the final price of a product.

Be careful out there with NULL and use the techniques mentioned above!