Part 5: Mastering NULL Functions | SQL Challenges & Capstone
SQL Business Logic & Data Handling Practice · Part 5 · Lecture Notes
Mastering NULL Functions
How to replace missing values in query output using IFNULL and COALESCE — creating clean, standardized business reports without changing the database.
Part 5 of 683% Complete
IFNULL()
COALESCE()
NULL Replacement
Default Values
8 Practice Questions
Learning Objectives
Use IFNULL() to replace a single column’s NULL values with a meaningful default label
Use COALESCE() to check multiple values and return the first non-NULL one found
Combine NULL functions with ORDER BY and additional columns for complete business reports
Understand that NULL functions only affect query output — the database data is never changed
Click any section header to open or close it
NULL functions replace missing values with alternative values inside query results. They do not change any data in the database — they only substitute a readable label in the output wherever a NULL would otherwise appear. The result is cleaner, more complete reports that non-technical stakeholders can read without confusion.
In industry, analysts use NULL functions to clean incomplete business data, standardize report outputs, replace missing values with default business labels, improve dashboard readability, and prevent empty cells in exported reports.
Two Functions — IFNULL and COALESCE
In MySQL, IFNULL(column, replacement) checks one column and returns the replacement if it is NULL. COALESCE(column1, column2, replacement) checks a list of values and returns the first non-NULL one — making it more flexible for complex scenarios.
Basic IFNULL Syntax
SELECT IFNULL(column_name, ‘replacement_value’)
FROM table_name;
Basic COALESCE Syntax
SELECT COALESCE(column_name, ‘replacement_value’)
FROM table_name;
IFNULL takes two arguments: the column to check, and the value to return if that column is NULL. If the column contains a real value, IFNULL returns the real value unchanged. If the column is NULL, IFNULL returns the replacement. It is the most direct NULL handling function for single-column scenarios.
Reading IFNULL
IFNULL(end_date, ‘Active Subscription’) reads as: “return end_date if it exists, otherwise return the text Active Subscription.” The replacement can be any text, number, or date value.
Replace NULL end_date With a Label
SELECT
subscription_id,
IFNULL(end_date, ‘Active Subscription’) AS subscription_end
FROM subscriptions;
Replace NULL resolved_date With a Label
SELECT
ticket_id,
IFNULL(resolved_date, ‘Not Resolved’) AS resolution_status
FROM support_tickets;
COALESCE checks a list of values from left to right and returns the first one that is not NULL. For simple two-argument cases it produces the same result as IFNULL. Its real power comes when multiple fallback options exist — COALESCE(field1, field2, field3, ‘default’) tries each in order and returns the first real value found.
COALESCE vs IFNULL
For single-column NULL replacement, both produce identical results. Use IFNULL when you have one column and one fallback. Use COALESCE when you have multiple columns to try, or when writing SQL that may run on databases other than MySQL (COALESCE is ANSI standard; IFNULL is MySQL-specific).
COALESCE on end_date — Ongoing Label
SELECT
subscription_id,
COALESCE(end_date, ‘Ongoing’) AS subscription_status
FROM subscriptions;
COALESCE on resolved_date — Pending Label
SELECT
ticket_id,
COALESCE(resolved_date, ‘Pending Resolution’) AS ticket_resolution
FROM support_tickets;
NULL functions can be combined with every other SQL clause. Adding ORDER BY lets analysts sort the cleaned output. Including other columns alongside the NULL function gives full business context. These combinations are standard in subscription reporting and support queue management.
Subscription Reporting Pattern
Combining IFNULL(end_date, ‘Active Member’) with status and ORDER BY monthly_fee DESC produces a complete, sorted subscription report where NULL end dates are clearly labelled — ready for a business dashboard without any manual cleanup.
Full Subscription Report With NULL Handling
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee,
IFNULL(end_date, ‘Subscription Active’) AS subscription_end
FROM subscriptions
ORDER BY monthly_fee DESC;
Support Tickets Sorted by Created Date
SELECT
ticket_id,
created_date,
COALESCE(resolved_date, ‘Awaiting Resolution’) AS resolution_status
FROM support_tickets
ORDER 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 1Replace NULL end_date values with Active Subscription in the subscriptions table
AI Prompt
Write a MySQL query to replace NULL end_date values with Active Subscription in the subscriptions table.
SQL Answer
SELECT
subscription_id,
IFNULL(end_date, ‘Active Subscription’) AS subscription_end
FROM subscriptions;
Practice 2Replace NULL resolved_date values with Not Resolved in the support_tickets table
AI Prompt
Write a MySQL query to replace NULL resolved_date values with Not Resolved in the support_tickets table.
SQL Answer
SELECT
ticket_id,
IFNULL(resolved_date, ‘Not Resolved’) AS resolution_status
FROM support_tickets;
Practice 3Use COALESCE to replace NULL end_date values with Ongoing in the subscriptions table
AI Prompt
Write a MySQL query using COALESCE to replace NULL end_date values with Ongoing in the subscriptions table.
SQL Answer
SELECT
subscription_id,
COALESCE(end_date, ‘Ongoing’) AS subscription_status
FROM subscriptions;
Practice 4Use COALESCE to replace NULL resolved_date values with Pending Resolution in the support_tickets table
AI Prompt
Write a MySQL query using COALESCE to replace NULL resolved_date values with Pending Resolution in the support_tickets table.
SQL Answer
SELECT
ticket_id,
COALESCE(resolved_date, ‘Pending Resolution’) AS ticket_resolution
FROM support_tickets;
Practice 5Replace NULL end_date values with Active Member and display status from the subscriptions table
AI Prompt
Write a MySQL query to replace NULL end_date values with Active Member and display status from the subscriptions table.
SQL Answer
SELECT
subscription_id,
status,
IFNULL(end_date, ‘Active Member’) AS subscription_end
FROM subscriptions;
Practice 6Replace NULL resolved_date values with Awaiting Resolution and sort by created_date
AI Prompt
Write a MySQL query to replace NULL resolved_date values with Awaiting Resolution and sort by created_date.
SQL Answer
SELECT
ticket_id,
created_date,
COALESCE(resolved_date, ‘Awaiting Resolution’) AS resolution_status
FROM support_tickets
ORDER BY created_date ASC;
Practice 7Replace NULL end_date values with Subscription Active and sort by monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to replace NULL end_date values with Subscription Active and sort by monthly_fee rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee,
IFNULL(end_date, ‘Subscription Active’) AS subscription_end
FROM subscriptions
ORDER BY monthly_fee DESC;
Practice 8Use COALESCE to replace NULL resolved_date values with Open Ticket from the support_tickets table
AI Prompt
Write a MySQL query using COALESCE to replace NULL resolved_date values with Open Ticket from the support_tickets table.
SQL Answer
SELECT
ticket_id,
COALESCE(resolved_date, ‘Open Ticket’) AS ticket_status
FROM support_tickets;
Knowledge Check
Five questions — select the best answer for each.
1. What does IFNULL(end_date, ‘Active Subscription’) do?
AIt deletes rows where end_date is NULL
BIt returns end_date if it exists, and Active Subscription if end_date is NULL
CIt filters subscriptions that are currently active
DIt renames the end_date column to Active Subscription permanently
Correct. IFNULL checks the first argument — if it has a value, return it; if it is NULL, return the second argument instead. The database is unchanged.
Not quite. IFNULL returns the column value if it exists, or the replacement text if the column is NULL — it only affects the query output.
2. What is the key difference between IFNULL and COALESCE?
AIFNULL works on dates only; COALESCE works on text
BCOALESCE can check multiple values and return the first non-NULL; IFNULL checks one column with one fallback
CCOALESCE permanently updates NULL values; IFNULL does not
DIFNULL is faster than COALESCE on all queries
Correct. COALESCE accepts a list of arguments and returns the first non-NULL value found. IFNULL takes exactly two arguments. For simple single-column replacement, both produce the same result.
Not quite. COALESCE can evaluate a list of multiple values and return the first non-NULL one. IFNULL checks one column with one fallback.
3. Does IFNULL or COALESCE change any data in the database?
AYes — they update NULL values to the replacement permanently
BYes — but only for the duration of the session
CNo — they only affect the output of the query
DIt depends on whether you include ORDER BY
Correct. NULL functions only affect what is displayed in the query result. The underlying table data is completely unchanged — every NULL value is still NULL in the database.
Not quite. NULL functions only affect query output — they never modify the database. The NULL values remain in the table.
4. An analyst writes COALESCE(resolved_date, ‘Pending Resolution’). When does Pending Resolution appear in the output?
AWhen resolved_date contains the text Pending
BWhen resolved_date is NULL
CWhen resolved_date is greater than today’s date
DWhen resolved_date equals zero
Correct. COALESCE returns the first non-NULL value in its list. If resolved_date is NULL, it moves to the next argument — the text ‘Pending Resolution’ — and returns that instead.
Not quite. COALESCE returns the replacement text when the column is NULL — that is the only condition.
5. Which MySQL function is ANSI SQL standard and works across all major database systems?
AIFNULL
BISNULL
CCOALESCE
DNULLIF
Correct. COALESCE is the ANSI SQL standard function for NULL replacement and works across MySQL, PostgreSQL, SQL Server, Oracle, and all major databases. IFNULL is MySQL-specific.
Not quite. COALESCE is the ANSI SQL standard — it works across all major database systems. IFNULL is MySQL-specific.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
IFNULL and COALESCE help analysts replace missing business data and create cleaner, more standardized SQL outputs. IFNULL checks one column with one fallback. COALESCE checks a list and returns the first non-NULL value. Neither changes the database.
IFNULLCOALESCENULL ReplacementMissing DataDefault ValuesReport CleaningSubscriptionsSupport TicketsANSI Standard
