COALESCE & NULL Handling in SQL

 

🧠 1. What is NULL in SQL?  

NULL means  : 

• 👉 missing value  
• 👉 unknown value
• 👉 no data available  

⚠️ NULL is NOT:  

❌ 0  

❌ Empty string  

⚡ 2. Problems with NULL Values  

NULL can affect:  

• ❌ Calculations  
• ❌ Comparisons
• ❌ Reports  

Example 👇

SELECT salary + bonus

FROM employees;

If bonus is NULL → result becomes NULL ❌  

========================================================

🔥 3. COALESCE Function  

👉 Replaces NULL with another value  

✅ Syntax

COALESCE(column, value)

⚡ 4. Basic Example

SELECT name,

       COALESCE(bonus, 0) AS bonus

FROM employees;

✔️ If bonus is NULL → shows 0 instead  

⚡ 5. Multiple Values with COALESCE

SELECT name,

       COALESCE(phone, email, 'No Contact') AS contact

FROM employees;

✔️ Returns first non-NULL value  

🔥 6. IS NULL & IS NOT NULL  

👉 Find employees without bonus

SELECT * FROM employees

WHERE bonus IS NULL;

👉 Find employees with bonus

SELECT * FROM employees

WHERE bonus IS NOT NULL;

⚡ 7. NULL Handling in Aggregation

SELECT AVG(COALESCE(bonus, 0))

FROM employees;

✔️ Prevents NULL issues in calculations  

🎯 8. Practice Tasks  

1. Replace NULL bonus with 0  

2. Find rows with NULL values  

3. Find rows without NULL values  

4. Use COALESCE with multiple columns  

5. Calculate total salary safely using COALESCE  

⚡ Mini Challenge 🔥  

👉 Show employee total income:  

salary + bonus  

(If bonus is NULL → treat as 0)  

🔥 Mini Challenge Solution 💯

👉 Requirement:  

total_income = salary + bonus  

If bonus is NULL → treat it as 0  

✅ SQL Solution Using COALESCE

SELECT name,

       salary,

       COALESCE(bonus, 0) AS bonus,

       salary + COALESCE(bonus, 0) AS total_income

FROM employees;

✅ Example Output  

name : Amit, salary : 70000, bonus : 5000, total_income : 75000  

name : Neha, salary : 50000, bonus : 0, total_income : 50000  

name : Ravi, salary : 60000, bonus : 3000, total_income : 63000  

🧠 How It Works  

👉 COALESCE(bonus, 0)  

means:  

- If bonus exists → use bonus  

- If bonus is NULL → use 0