Part 3: Mastering NULL Values | SQL Challenges & Capstone
SQL Business Logic & Data Handling Practice · Part 3 · Lecture Notes
Mastering NULL Values
How to identify and filter missing business data — using IS NULL and IS NOT NULL to handle incomplete records in subscriptions, support tickets, and beyond.
Part 3 of 650% Complete
IS NULL
IS NOT NULL
Missing Data
Data Quality
8 Practice Questions
Learning Objectives
Understand what NULL means — missing, unknown, or unavailable data — and how it differs from zero or empty text
Use IS NULL to filter records where a column has no recorded value
Use IS NOT NULL to filter records where a column contains an actual value
Combine IS NULL and IS NOT NULL with AND and ORDER BY for practical business queries
Click any section header to open or close it
NULL represents missing, unknown, or unavailable data inside a database. It is not a value — it is the absence of a value. Understanding NULL is critical for any analyst working with real business data, because real datasets almost always contain incomplete records.
In industry, analysts encounter NULL values when customers leave fields empty, subscriptions have no recorded end date, support tickets remain unresolved, business data has not yet been entered, or information simply does not exist for that record.
NULL Is Not Zero or Empty Text
NULL is not 0. NULL is not an empty string. NULL is not false. It means no value exists at all. A subscription with end_date = NULL has no end date recorded — not a zero date, not a blank date, not the date 0000-00-00. Treating NULL as any of these equivalents produces incorrect analysis.
IS NULL is the correct way to filter for missing values in SQL. You cannot use = NULL — that syntax never matches anything because NULL cannot be compared with standard equality operators. IS NULL is the only operator that correctly identifies missing values.
Why = NULL Never Works
In SQL, NULL = NULL evaluates to NULL — not true. So WHERE end_date = NULL returns no rows, even if every row has a NULL end_date. IS NULL is specifically designed to detect the absence of a value where = cannot.
Subscriptions With No End Date
SELECT *
FROM subscriptions
WHERE end_date IS NULL;
Unresolved Support Tickets
SELECT *
FROM support_tickets
WHERE resolved_date IS NULL;
IS NOT NULL is the complement of IS NULL — it returns only rows where a value actually exists in the specified column. Analysts use it to isolate complete records, identify transactions that have been processed, find tickets that have been resolved, or locate subscriptions with an end date on record.
Pair IS NULL and IS NOT NULL for Data Quality Checks
Running IS NULL and IS NOT NULL on the same column is a standard data quality check. The row counts from both queries should add up to the total rows in the table. Any discrepancy signals unexpected data issues.
Subscriptions With a Recorded End Date
SELECT *
FROM subscriptions
WHERE end_date IS NOT NULL;
Resolved Support Tickets
SELECT *
FROM support_tickets
WHERE resolved_date IS NOT NULL;
IS NULL and IS NOT NULL work alongside every other SQL clause. Adding AND lets you narrow NULL checks to a specific business subset. Adding ORDER BY organizes the results for reporting. These combinations are standard in subscription analysis, support queue management, and data quality work.
Practical Combinations
WHERE end_date IS NULL AND status = ‘Active’ isolates currently active subscriptions. WHERE resolved_date IS NULL ORDER BY created_date ASC shows the oldest unresolved tickets first — a priority queue pattern used in support teams.
Active Subscriptions With No End Date
SELECT *
FROM subscriptions
WHERE end_date IS NULLAND status = ‘Active’;
Unresolved Tickets — Oldest First
SELECT *
FROM support_tickets
WHERE resolved_date IS NULLORDER BY created_date ASC;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve subscriptions with missing end_date values
AI Prompt
Write a MySQL query to show subscriptions with missing end_date values.
SQL Answer
SELECT *
FROM subscriptions
WHERE end_date IS NULL;
Practice 2Retrieve subscriptions with recorded end_date values
AI Prompt
Write a MySQL query to show subscriptions with recorded end_date values.
SQL Answer
SELECT *
FROM subscriptions
WHERE end_date IS NOT NULL;
Practice 3Retrieve support tickets with missing resolved_date values
AI Prompt
Write a MySQL query to show support tickets with missing resolved_date values.
SQL Answer
SELECT *
FROM support_tickets
WHERE resolved_date IS NULL;
Practice 4Retrieve support tickets with resolved dates
AI Prompt
Write a MySQL query to show support tickets with resolved dates.
SQL Answer
SELECT *
FROM support_tickets
WHERE resolved_date IS NOT NULL;
Practice 5Retrieve active subscriptions with missing end_date values
AI Prompt
Write a MySQL query to show active subscriptions with missing end_date values.
SQL Answer
SELECT *
FROM subscriptions
WHERE end_date IS NULLAND status = ‘Active’;
Practice 6Retrieve cancelled subscriptions with recorded end_date values
AI Prompt
Write a MySQL query to show cancelled subscriptions with recorded end_date values.
SQL Answer
SELECT *
FROM subscriptions
WHERE end_date IS NOT NULLAND status = ‘Cancelled’;
Practice 7Retrieve unresolved support tickets sorted by created_date
AI Prompt
Write a MySQL query to show unresolved support tickets sorted by created_date.
SQL Answer
SELECT *
FROM support_tickets
WHERE resolved_date IS NULLORDER BY created_date ASC;
Practice 8Retrieve resolved support tickets sorted by resolved_date
AI Prompt
Write a MySQL query to show resolved support tickets sorted by resolved_date.
SQL Answer
SELECT *
FROM support_tickets
WHERE resolved_date IS NOT NULLORDER BY resolved_date ASC;
Knowledge Check
Five questions — select the best answer for each.
1. What does NULL represent in a database?
AThe number zero
BAn empty string with no characters
CMissing, unknown, or unavailable data
DA false boolean value
Correct. NULL means no value exists — it is not zero, not empty text, not false. It represents the complete absence of recorded information for that field.
Not quite. NULL represents missing or unknown data — it is not zero, not empty text, and not false.
2. Which query correctly finds subscriptions with no end_date recorded?
AWHERE end_date = NULL
BWHERE end_date = ”
CWHERE end_date IS NULL
DWHERE end_date == NULL
Correct. IS NULL is the only operator that correctly detects missing values in SQL. WHERE end_date = NULL never returns any rows because NULL cannot be compared with equality operators.
Not quite. IS NULL is the correct syntax — WHERE end_date = NULL always returns zero rows because NULL cannot be compared with =.
3. What does IS NOT NULL return?
ARows where the column contains the text NOT NULL
BRows where the column has no value
CRows where the column contains an actual value
DRows where the column equals zero
Correct. IS NOT NULL filters in the opposite direction from IS NULL — it returns only rows where a value actually exists in the specified column.
Not quite. IS NOT NULL returns rows where a value is present — the column is not missing.
4. An analyst runs two queries: one with WHERE end_date IS NULL and one with WHERE end_date IS NOT NULL. What should the row counts from both queries add up to?
AZero — NULL rows are excluded from all counts
BThe total number of rows in the subscriptions table
CDouble the total — because both queries run twice
DThe count depends on the ORDER BY clause
Correct. Every row either has a NULL end_date or a non-NULL end_date — there is no third option. The two row counts must sum to the table’s total row count, making this a reliable data quality check.
Not quite. Every row is either NULL or not NULL in a given column. The counts from IS NULL and IS NOT NULL must add up to the total number of rows.
5. Which query returns unresolved support tickets sorted oldest first?
ASELECT * FROM support_tickets WHERE resolved_date = NULL ORDER BY created_date DESC;
BSELECT * FROM support_tickets WHERE resolved_date IS NULL ORDER BY created_date ASC;
CSELECT * FROM support_tickets ORDER BY created_date IS NULL;
DSELECT * FROM support_tickets WHERE resolved_date IS NOT NULL ORDER BY created_date ASC;
Correct. IS NULL finds unresolved tickets. ORDER BY created_date ASC sorts oldest to newest — placing the longest-waiting tickets at the top for triage priority.
Not quite. WHERE resolved_date IS NULL finds unresolved tickets. ORDER BY created_date ASC sorts oldest first. Option B is correct.
Part 3 Summary
The key ideas from this lecture — keep these in mind as you move forward.
NULL values represent missing business data, and analysts use IS NULL and IS NOT NULL to identify incomplete or available information. Never use = NULL — it never matches. IS NULL and IS NOT NULL are the only correct operators for detecting missing values.
NULLIS NULLIS NOT NULLMissing DataData QualityANDORDER BYSubscriptionsSupport Tickets
