SQL COALESCE, NULLIF, and NVL Functions

💻 Introduction

Handling NULL values is one of the most important aspects of SQL.
Functions like COALESCE, NULLIF, and NVL help you manage NULL values effectively and write cleaner queries.

—

1️⃣ SQL COALESCE()

The COALESCE() function returns the first non-NULL value from a list of expressions.

SELECT EmpID, 
       COALESCE(Phone, Mobile, 'No Contact') AS ContactNumber
FROM Employees;

• If Phone is not NULL → return Phone.
• If Phone is NULL but Mobile is not → return Mobile.
• If both are NULL → return ‘No Contact’.

—

2️⃣ SQL NULLIF()

The NULLIF() function compares two expressions.
If they are equal, it returns NULL, otherwise it returns the first expression.

SELECT EmpID, 
       Salary / NULLIF(DepartmentCount, 0) AS AvgSalaryPerDept
FROM Employees;

• Prevents division by zero errors.
• If DepartmentCount = 0 → returns NULL instead of an error.
• If not equal → returns Salary / DepartmentCount.

—

3️⃣ SQL NVL() (Oracle Only)

The NVL() function is used in Oracle SQL to replace NULL with a specified value.

SELECT EmpID, NVL(Bonus, 0) AS Bonus
FROM Employees;

• If Bonus is NULL → returns 0.
• If Bonus has a value → returns that value.

—

✅ Key Takeaways

• COALESCE() → Returns first non-NULL value.
• NULLIF() → Returns NULL if values are equal (avoid errors).
• NVL() → Oracle-specific NULL handling.
• These functions improve query reliability and handle missing data gracefully.

—

📚 Continue Learning