NULL-Safe Filtering — IS NULL and IS NOT NULL

In the beginner course, you learned what NULL values are and how missing data appears in a table. Now we focus on how to correctly filter rows that contain NULL values.

NULL represents missing or unknown data. It does not behave like a normal value, so it must be handled differently in SQL.

This pattern is perfect for:

• Finding missing data
• Filtering incomplete records
• Checking for optional fields
• Avoiding incorrect comparisons

Basic Pattern

SELECT column
FROM table_name
WHERE column IS NULL;

SELECT column
FROM table_name
WHERE column IS NOT NULL;

You must use IS NULL or IS NOT NULL. Using = NULL will not work.

Example 1: Students without an email

SELECT student_id,
       first_name,
       last_name
FROM students
WHERE email IS NULL;

This shows students with missing email information.

The result shows 0 rows because none of the students in the table have a NULL value in the email column—all records contain an email address.

Example 2: Students with an email

SELECT student_id,
       first_name,
       last_name
FROM students
WHERE email IS NOT NULL;

This returns only students with valid email values.

Example 3: Enrollments without a grade

SELECT enrollment_id,
       student_id,
       course_id
FROM enrollments
WHERE grade IS NULL;

This identifies records where grades have not been assigned.

The result shows 0 rows because all enrollment records in the table have a grade value, so there are no NULL values in the grade column.

Example 4: Purchase orders not yet delivered (using NULL logic idea)

SELECT enrollment_id,
       student_id,
       course_id,
       grade
FROM enrollments
WHERE grade IS NOT NULL;

This filters only completed records.

Why Use NULL-Safe Filtering?

• NULL cannot be compared using = or <>
• It ensures accurate filtering of missing data
• It prevents incorrect query results
• It is essential when working with real-world datasets

NULL-Safe Filtering Summary

Feature	Description
Purpose	Filter missing or known values
Operators	IS NULL, IS NOT NULL
Common mistake	Using = NULL (does not work)
Strength	Accurate handling of missing data

A NULL check is like asking:

“Is the value missing or not?”

Practice Tasks (Your Turn!)

Task 1: Students Table

Show students who do not have a program listed.

Task 2: Enrollments Table

Show enrollments where the grade is missing.

Task 3: Courses Table

Show courses where the department is provided.