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  

  

✅ CASE Statement in SQL

 

✅ CASE Statement in SQL  

🧠 1. What is CASE Statement?

• CASE is used for conditional logic in SQL
• It's like if-else in programming
• Helps categorize data
• Create custom labels
• Build smart reports  

⚡ 2. Basic Syntax

SELECT column_name,

       CASE

           WHEN condition THEN result

           ELSE result

       END AS alias_name

FROM table_name;

📊 Example Table  

name : Ajit, Neha, Sujit  

salary : 70000, 40000, 55000  

🔥 3. Basic CASE Example  

👉 Categorize employees by salary

SELECT name, salary,

       CASE

           WHEN salary >= 60000 THEN 'High Salary'

           WHEN salary >= 50000 THEN 'Medium Salary'

           ELSE 'Low Salary'

       END AS salary_category

FROM employees;

✅ Output  

name : Ajit, Sujit, Neha  

salary : 70000, 55000, 40000  

salary_category : High Salary, Medium Salary, Low Salary  

⚡ 4. CASE with Aggregation  

👉 Count employees department-wise

SELECT department,

       SUM(

           CASE

               WHEN salary > 50000 THEN 1

               ELSE 0

           END

       ) AS high_salary_count

FROM employees

GROUP BY department;

⚡ 5. CASE in ORDER BY

SELECT name, department

FROM employees

ORDER BY

CASE

    WHEN department = 'IT' THEN 1

    WHEN department = 'HR' THEN 2

    ELSE 3

END;

🎯 6. Practice Tasks

1. Categorize salary as High/Low

2. Create age groups using CASE

3. Use CASE with GROUP BY

4. Count employees based on salary condition

5. Use CASE in ORDER BY

What will this query return?

SELECT name, salary,

       CASE

           WHEN salary >= 50000 THEN 'High'

           ELSE 'Low'

       END AS category

FROM employees;

A. Deletes salaries

B. Sorts employees

C. Add employees

D. Categorizes salary as High or Low

Ans : D

✅ UNION & UNION ALL in SQL

 ✅ UNION & UNION ALL in SQL  

🧠 1. What is UNION?  

UNION is used to combine results from multiple SELECT queries  

"Merge data from two tables into one result”  

⚡ 2. Rules for UNION  

•  Same number of columns  
•  Same datatype/order of columns  

📊 Example Tables  

👨‍💼 employees_2025

Name  

- Ajit

- Neha  

👨‍💼 employees_2026  

name  

- Sujit 

- Neha  

🔥 3. UNION Example

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2025;

✔️ Removes duplicates automatically  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

⚡ 4. UNION ALL

SELECT name FROM employees_2025

UNION ALL

SELECT name FROM employees_2026;

• Keeps duplicates  
• Faster than UNION  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

- Neha  

🔥 5. UNION vs UNION ALL  

UNION  

•  Removes duplicates
•  Slower  
• Doesn't keep all rows  

UNION ALL  

• Doesn't remove duplicates  
• Faster  
• Keeps all rows  

⚡ 6. ORDER BY with UNION

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2026

ORDER BY name;

🎯 7. Practice Tasks  

1. Combine employee names using UNION  

2. Combine employee names using UNION ALL  

3. Identify duplicate removal  

4. Sort UNION result using ORDER BY  

5. Compare UNION vs UNION ALL output  

🧠 Why UNION ALL?  

👉 UNION → removes duplicates  

👉 UNION ALL → keeps duplicates + faster

#union #union_alll