When SQL Has Nothing to Say: Handling NULLs

Part 1 Recap

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()

The 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 it's NULL, 'unknown' is returned.

SELECT customer_name, COALESCE(home_address, work_address) FROM customers;

Checks home_address; if it's NULL, the value from work_address is returned.

SELECT customer_name, COALESCE(home_address, work_address, 'unknown') FROM customers;

Checks home_address. If it's 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, ISNULL() and COALESCE() have distinct differences in syntax, behavior, and portability.

NULLIF()

The NULLIF function compares two values and returns:

• NULL if they are equal
• the first value if they are not equal

NULLIF() accepts only 2 values.

Basic Syntax: NULLIF(value1, value2)

Use Case 1: Normalizing 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.

Use Case 2: Identifying 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.

Use Case 3: Avoiding 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.