COALESCE / IFNULL (Applied)

Overview

COALESCE / IFNULL — Replace NULL Values

COALESCE and IFNULL are used to replace NULL values with something more useful.

They help answer questions like:

• “If a student has no enrollments, show 0 instead of NULL.”
• “If an instructor has no department listed, show Unknown.”
• “If a grade is missing, show No Grade.”

These functions make your results cleaner and easier to read.

---

What COALESCE Does

COALESCE(value1, value2) returns the first non NULL value.

Example idea:

• COALESCE(NULL, 'Math') → Math
• COALESCE(NULL, NULL, 5) → 5

It checks each value in order and stops at the first one that isn’t NULL.

---

What IFNULL Does

IFNULL(value, replacement) returns:

• value if it is NOT NULL
• replacement if it IS NULL

It’s a simpler version of COALESCE with only two arguments.

---

Basic Patterns

COALESCE

COALESCE(column, replacement)
  

IFNULL

IFNULL(column, replacement)
  

For beginners, both work the same way.

---

Examples

Example 1: Replace NULL grades with “No Grade”

SELECT student_id,
       course_id,
       COALESCE(grade, 'No Grade') AS CleanGrade
FROM enrollments;
  

Explanation

• If grade is NULL → show No Grade
• If grade has a value → show the value

---

Example 2: Replace missing departments for instructors

SELECT first_name,
       last_name,
       IFNULL(department, 'Unknown') AS CleanDepartment
FROM instructors;
  

Explanation

• If department is NULL → show Unknown
• Otherwise → show the real department

---

Example 3: Replace NULL enrollment counts with 0

SELECT s.first_name,
       s.last_name,
       COALESCE((
           SELECT COUNT(*)
           FROM enrollments e
           WHERE e.student_id = s.student_id
       ), 0) AS TotalEnrollments
FROM students s;
  

Explanation

• If a student has no enrollments → COUNT returns NULL
• COALESCE replaces it with 0

---

Example 4: Replace NULL course credits with a default value

SELECT course_name,
       COALESCE(credits, 0) AS CleanCredits
FROM courses;
  

Useful when some courses have missing credit values.

---

Why COALESCE / IFNULL Are Useful

• They clean up NULL values
• They make reports easier to read
• They prevent confusing blanks
• They work well with subqueries and aggregates

---

COALESCE / IFNULL Summary

Function	Meaning	Beginner Explanation
COALESCE	Returns first non NULL value	“Use this value unless it’s NULL — then try the next one.”
IFNULL	Replace NULL with a value	“If it’s NULL, use this instead.”

---

Practice Tasks (Your Turn!)

Task 1

Replace NULL grades with “No Grade”.

Task 2

Replace NULL departments with “Unknown”.

Task 3

Replace NULL enrollment counts with 0.