ISNULL vs COALESCE in SQL Server: replace NULL with a value

NULL means “nobody filled this in”. Reports usually want a word or a zero instead. ISNULL swaps it for whatever you give it.

Syntax

ISNULL(column, replacement)

Example

SELECT customer_id,
       first_name,
       ISNULL(phone, 'No phone') AS phone
FROM Customers;

Zero instead of NULL in totals

SELECT p.product_name,
       ISNULL(r.units_returned, 0) AS units_returned
FROM Products p
LEFT JOIN (SELECT product_id, SUM(quantity) AS units_returned
           FROM Returns GROUP BY product_id) r
  ON r.product_id = p.product_id;

ISNULL or COALESCE?

• COALESCE(a, b, c) takes any number of arguments and returns the first that isn’t NULL. ISNULL takes exactly two.
• COALESCE is standard SQL and works in other databases. ISNULL is SQL Server only.
• ISNULL returns the type of the first argument, so a long replacement can be cut short to fit the column’s length. COALESCE picks a type that fits all its arguments.

In day-to-day SQL Server reports either is fine. Pick one and be consistent.

Testing for NULL

To filter, use WHERE phone IS NULL. = NULL is never true, not even for NULL.