🧪 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

✅ Recommended	❌ Avoid This
Use COALESCE() for multiple fallbacks	Nesting ISNULL() when COALESCE() works better
Test NULLs with IS NULL	Using = NULL or != NULL (invalid syntax)
Use NULLIF() in divide-by-zero logic	Using CASE unnecessarily

---

📌 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.

---

Share Now :