🧠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
No comments:
Post a Comment