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!