SQL NULL Functions – COALESCE, ISNULL, NULLIF Explained

Introduction – Why Use NULL Functions?

In SQL, NULL represents missing or unknown data. NULL functions help you manage, replace, and handle NULLs without breaking your logic or calculations.

In this guide, you’ll learn:

• What common SQL NULL functions do
• Syntax and usage of ISNULL(), COALESCE(), and NULLIF()
• Real-world examples for replacing NULLs and preventing divide-by-zero errors

1. IS NULL / IS NOT NULL (Test NULL Values)

SELECT * FROM users
WHERE email IS NULL;

Filters rows where a column contains NULL.

WHERE age IS NOT NULL;

Filters out rows with missing age values.

2. ISNULL() – SQL Server / MS Access

SELECT ISNULL(email, 'no-email@example.com') AS contact_email
FROM users;

Replaces NULL with a default value.

Equivalent to IFNULL() in MySQL.

3. COALESCE() – Standard SQL

SELECT COALESCE(phone, alternate_phone, 'N/A') AS contact_number
FROM customers;

Returns the first non-NULL value in the list.

Cross-platform and preferred for multiple fallback values.

4. NULLIF() – Return NULL When Two Values Are Equal

SELECT NULLIF(actual, expected) AS variance
FROM results;

Returns NULL if actual = expected; otherwise returns actual.

Useful to prevent division by zero:

SELECT amount / NULLIF(quantity, 0) FROM inventory;

5. Common Use Cases

• Replace NULLs with readable labels
• Avoid divide-by-zero or error-prone calculations
• Provide fallback values in dashboards
• Normalize inconsistent or optional fields

Best Practices

Summary – Recap & Next Steps

NULL functions are essential tools for building resilient, readable SQL queries that handle incomplete or optional data smoothly.

Key Takeaways:

• ISNULL() and IFNULL() replace NULL with a default value
• COALESCE() returns the first non-NULL in a list
• NULLIF() avoids math errors by comparing values
• Use IS NULL and IS NOT NULL to filter properly

Real-World Relevance:
Used in dashboards, financial calculations, data cleaning pipelines, and business rules.

FAQ – SQL NULL Functions

What’s the difference between ISNULL() and COALESCE()?

ISNULL() works with two values; COALESCE() can take many and is more portable.

Does COALESCE() stop evaluating after a match?

Yes. It returns the first non-NULL and skips the rest.

Can I use NULLIF to avoid zero division?

Yes. Use NULLIF(quantity, 0) in the denominator.

Is ISNULL portable across databases?

No. Use COALESCE() for better cross-database compatibility.

What happens if all COALESCE values are NULL?

The result is NULL.