When SQL Has Nothing to Say: Handling NULLs

In Part 1 of the NULL series, we focused on:

• what NULL represents in a dataset: a value that is missing, unknown, or not applicable

• how to use is null and is not null to find, update and delete rows with NULL.

NULL values can cause unexpected behavior in comparisons and calculations, so it's important to know how to handle them effectively.

In Part 2, we'll learn how to:

• replace NULL with a specified/default value using isnull() and coalesce()

• replace a value with NULL using nullif()

Note: isnull() is database-specific. The examples below use isnull() to demonstrate the function, but PostgreSQL does not support it. PostgreSQL uses coalesce() for this purpose.

ISNULL()

isnull() replaces NULL with a specified value, ensuring queries return a value even when data is missing.
It takes two arguments: the expression to check and the replacement value if that expression is NULL.

Basic Syntax;

isnull(expression, replacement_value)

expression: The value or column to check for NULL.
replacement_value: The value returned if the expression is NULL.

select customer_name,
isnull(home_address, 'unknown')
from customers;

If NULLs are present in the home_address column, they are replaced with the default value - 'unknown'

select customer_name,
isnull(home_address, work_address)
from customers;

We might also want to use another column's value as the replacement for NULLs.
In this example, if home_address is NULL, the value from work_address is used instead.

With a default value, you're certain the output will not have any NULLs.
But with column replacement, if the replacement value is also NULL, you'll still get a NULL in the output.

COALESCE()

Coalesce function is commonly used for handling NULLs.
It evaluates a list of expressions in a specified order and returns the first non-null value encountered.

Basic Syntax;

coalesce(value1, value2, value3,...)

select customer_name,
coalesce(home_address, 'unknown')
from customers;

Checks home_address. if its NULL, 'unknown' is returned.

select customer_name,
coalesce(home_address, work_address)
from customers;

Checks home_address. if its NULL, the value from work_address is returned.

select customer_name,
coalesce(home_address, work_address, 'unknown')
from customers;

Checks home_address. If its NULL, it moves to work_address. If work_address is also NULL, it uses the default value - 'unknown'.

This is where coalesce() becomes especially useful: you can provide multiple fallback values.

Although they serve similar purposes, they have distinct differences in syntax, behavior and portability.

NULLIF()

The NULLIF function compares two values and returns:

• NULL if they are equal

• first value if they are not equal

nullif() accepts only 2 values

Basic Syntax;

nullif(value1, value2)

Use cases:

1. Using nullif() to normalize values
Imagine a dataset where -1 has been used to represent an invalid or unavailable price.

select product_id, product_name, price,
nullif(price, -1) as price_cleaned
from product;

SQL checks the price column:

• if price != -1, the original price is returned.

• if price = -1, NULL is returned

If -1 isn't a valid price, we'd rather represent it as NULL.

In this case we're replacing a specific value with NULL, unlike coalesce() and isnull(), which replace NULL with another value.

2. Using nullif to identify special cases in the data

select product_id, product_name,
nullif(original_price, dicount_price) as price_check
from product;

If original_price and discount_price are equal, NULL is returned.

This can be useful when you're interested in identifying cases where two values are the same. Whether that indicates a data issue depends on the business rules.

3. Using nullif to avoid division-by-zero errors

select product_name,
sales_amount / nullif(quantity, 0) as unit_price
from orders;

Here, if quantity = 0, NULLIF() returns NULL, preventing a divide-by-zero error.

We'll get a NULL answer instead of an error

Conclusion

Understanding what NULL means in a dataset and knowing how to handle it effectively helps us avoid unexpected results and get more accurate insights from our data.

We've seen how isnull() and coalesce() can replace NULL with specified or fallback values, while nullif() can replace specific values with NULL and help us avoid division-by-zero errors.

So, When SQL has nothing to say, again?, just handle it wisely and it'll give you the right insights.

Comments

[Eusebiu Balan]

The one that got me was not a function, it was a comparison. where status != 'done' reads like it returns everything else, and it drops every row where status is null, because comparing anything with null gives null and null is not a match.

coalesce(status, 'pending') in the where clause fixes it. Worth putting next to is null in the series, because that bug never throws.

[Shaquille Mburu]

The examples make null-handling much clearer.