ISNULL vs COALESCE in SQL: The Ultimate Guide with Examples

Handling NULL values is one of the most common challenges in SQL queries. If you have ever worked with SQL Server or other relational databases, you’ve probably come across the functions ISNULL and COALESCE.

At first glance, they may look similar because both help you deal with NULL values. But the way they work, and the situations where you should use them, are not always the same.

In this blog, we’ll break down ISNULL vs COALESCE, use real-world examples, and explain when to use each.

---

What is ISNULL in SQL?

The ISNULL function takes two arguments. If the first one is NULL, it returns the second one. If the first one is not NULL, it simply returns that value.

Syntax:

ISNULL(expression, replacement_value)

Examples:

ISNULL(NULL, 'Delhi') → 'Delhi'  
ISNULL('Mumbai', 'Delhi') → 'Mumbai'  

This makes ISNULL very straightforward when you only need a simple fallback value.

---

What is COALESCE in SQL?

The COALESCE function can take multiple arguments. It checks each value in order and returns the first non-NULL value it finds.

Syntax:

COALESCE(expr1, expr2, expr3, ... exprN)

Examples:

COALESCE(NULL, NULL, 'Chennai', 'Delhi') → 'Chennai'  
COALESCE(NULL, NULL, NULL, 'Unknown') → 'Unknown'

This gives COALESCE more flexibility than ISNULL. You are not limited to only two arguments; you can pass multiple expressions and ensure a proper fallback.

---

ISNULL vs COALESCE in Practice

Consider this query:

SELECT
    e.City AS "employee's city",
    c.City AS "customer's city",
    ISNULL(e.City, c.City) AS "City",
    COALESCE(e.City, c.City, 'Unknown') AS "City",
    COUNT(DISTINCT e.EmployeeID) AS "total employees",
    COUNT(DISTINCT c.CustomerID) AS "total customers"
FROM Employees e
FULL OUTER JOIN Customers c
ON e.City = c.City
GROUP BY e.City, c.City
ORDER BY e.City;

Here’s what happens:

• ISNULL(e.City, c.City) will only check if employee’s city is NULL and, if so, replace it with the customer’s city.

• COALESCE(e.City, c.City, 'Unknown') goes one step further. If both employee’s city and customer’s city are NULL, it still returns 'Unknown'.

This makes COALESCE a safer and more flexible choice for handling NULL values.

---

Other Use Cases of ISNULL and COALESCE

1. Handling missing phone numbers or emails:

   COALESCE(HomePhone, MobilePhone, 'Not Provided')
   

2. Replacing NULL sales values with zero:

   ISNULL(SalesAmount, 0)
   

3. Building display names or labels:

   COALESCE(FirstName + ' ' + LastName, CompanyName, 'Anonymous')
   

4. Defaulting product descriptions:

   ISNULL(ProductDescription, 'No description available')
   

---

Key Differences Between ISNULL and COALESCE

• Number of Arguments: ISNULL only takes two arguments, COALESCE can take multiple.

• Portability: COALESCE is ANSI SQL standard and works across most databases, ISNULL is specific to SQL Server.

• Data Type Handling: ISNULL always returns the data type of the first argument, COALESCE determines the return type by evaluating all arguments.

---

Takeaway

• Use ISNULL when you just want a quick and simple replacement for NULL.

• Use COALESCE when you want flexibility, multiple fallback options, and database portability.

Understanding these two functions will help you write more reliable, clean, and professional SQL queries.

---

Final Thoughts

When it comes to ISNULL vs COALESCE, the choice depends on your use case. If you need a simple two-value fallback, ISNULL is fine. But if you need flexibility, readability, and better database compatibility, COALESCE is usually the better option.

Question for you: Do you use ISNULL more often, or COALESCE? And why? Share your experience in the comments.