COALESCE, ISNULL and NULLIF in SQL

NULL means “no value”: it isn’t zero or an empty string. This has consequences that surprise people at first: any operation involving a NULL returns NULL, and a report shows an empty cell where you probably expected a number. COALESCE solves both problems.

COALESCE(value1, value2, ..., valueN)

It returns the first value in the list that isn’t NULL. If all of them are, it returns NULL.

Replacing a NULL with a value

Customer Paul has no bank account on file. With COALESCE we can show some text instead. Since bankAccount is a number and the text isn’t, we first convert the account to text with CAST, because every value in COALESCE must have the same type:

Keeping a NULL from spoiling a calculation

If we do a LEFT JOIN from Customers and add up their orders, customers without orders get NULL instead of 0:

Jessie and Paul have no orders: SUM has nothing to add and returns NULL. With COALESCE(..., 0) the report shows a 0, which is what a reader expects.

Several fallback values

The advantage of COALESCE over other functions is that it takes any number of arguments and keeps the first one available. It’s handy, for example, to pick the best contact detail among several columns:

SELECT name,
       COALESCE(mobilePhone, landline, email, 'No contact') AS contact
FROM Customers;

COALESCE vs ISNULL

SQL Server also has ISNULL(value, replacement), which does the same with two arguments. The differences matter:

The last row causes the most bugs. ISNULL converts the replacement to the type of the first argument, so it can silently truncate text:

DECLARE @code VARCHAR(3) = NULL;

SELECT ISNULL(@code, 'No code') AS withIsnull,     -- 'No '
       COALESCE(@code, 'No code') AS withCoalesce; -- 'No code'

Unless you only work with SQL Server and need ISNULL for performance in some very specific case, use COALESCE.

NULLIF: the other way round

NULLIF(a, b) returns NULL if both values are equal, and a if they aren’t. Its classic use is avoiding division by zero: if the divisor is 0 it becomes NULL, and the division returns NULL instead of an error.

No order here has 0 units, so every row has a price. Try changing the 0 in NULLIF to 50 and both Echo DOTs become NULL. Combined with COALESCE you can also return a default: COALESCE(totalPrice / NULLIF(productQuantity, 0), 0).

Common mistakes

• Comparing with = NULL. WHERE bankAccount = NULL never returns rows. Use IS NULL or IS NOT NULL (see operators).
• Mixing types. COALESCE(bankAccount, 'No account') fails because bankAccount is numeric; convert it first with CAST.
• Using COALESCE in WHERE on an indexed column. WHERE COALESCE(bankAccount, 0) = 0 stops the engine from using an index on bankAccount. WHERE bankAccount = 0 OR bankAccount IS NULL is better.

Practice

1. List the orders, showing 'No customer' when customerId is NULL.
2. What does COALESCE(NULL, NULL, 3, 4) return? Try it in any of the editors.
3. For each customer, show the number of orders and the average spend, with 0 instead of NULL for customers who haven’t bought anything.