NULL handling and imputation (COALESCE, default values)

NULL Handling and Imputation — Filling Missing Values

In the beginner course, you learned how to identify NULL values. Now we focus on how to replace or handle them so your results are more complete and easier to read.

NULL handling (imputation) means replacing missing values with a default value using functions like COALESCE.

This pattern is perfect for:

• Replacing missing values
• Making reports easier to read
• Avoiding blank results
• Providing fallback values

Basic Pattern

SELECT column1,
       COALESCE(column_name, default_value) AS new_column
FROM table_name;

COALESCE returns the first non-NULL value.

Example 1: Replace missing program with ‘Not Assigned’

SELECT student_id,
       first_name,
       last_name,
       COALESCE(program, 'Not Assigned') AS program
FROM students;

If program is NULL, it will show ‘Not Assigned’.

Example 2: Replace missing grade with ‘Pending’

SELECT enrollment_id,
       student_id,
       course_id,
       COALESCE(grade, 'Pending') AS grade
FROM enrollments;

This makes missing grades easier to interpret.

Example 3: Replace missing email with a placeholder

SELECT student_id,
       first_name,
       last_name,
       COALESCE(email, 'no_email@unknown.com') AS email
FROM students;

This avoids blank fields in reports.

Example 4: Replace missing department in courses

SELECT course_id,
       course_name,
       COALESCE(department, 'General') AS department
FROM courses;

This ensures every row has a value.

Why Use NULL Handling and Imputation?

• It prevents missing values from appearing in results
• It improves readability of reports
• It provides meaningful defaults
• It avoids confusion when data is incomplete

NULL Handling and Imputation Summary

Feature	Description
Purpose	Replace missing values
Function	COALESCE
Returns	First non-NULL value
Best for	Reporting and cleaning data

COALESCE is like saying:

“If this value is missing, use something else instead.”

Practice Tasks (Your Turn!)

Task 1: Students Table

Replace missing program values with ‘Unknown’.

Task 2: Enrollments Table

Replace missing grades with ‘Not Graded’.

Task 3: Courses Table

Replace missing department values with ‘Other’.