MySQL CASE Statement with NULL Values: How to Check for NULL in CASE
MySQL CASE Statement with NULL Values, when working with data in MySQL, you will often encounter NULL values.
A NULL represents a missing, unknown, or unavailable value. If you need to handle these values conditionally, the CASE statement can be combined with IS NULL and IS NOT NULL.
For example, you can use the following query to replace NULL values in a column with a custom value:
SELECT id, team, points,CASEWHEN points ISNULLTHEN'Zero'ELSE pointsENDAS points_editedFROM athletes;
This query checks every value in the points column. If the value is NULL, MySQL returns 'Zero'. Otherwise, it returns the original value.
This technique is useful for data cleaning, reporting, analytics, dashboards, and SQL data transformation.
Example: How to Check for NULL in a CASE Statement in MySQL
Suppose we have a table named athletes containing basketball statistics.
We can create the table using:
CREATETABLE athletes ( id INTPRIMARYKEY, team TEXT, points INT, assists INT, rebounds INT);
Next, insert some sample data:
INSERTINTO athletes VALUES(1, 'Mavs', 22, 4, 3),(2, 'Kings', NULL, 5, 13),(3, 'Lakers', 37, 6, 10),(4, 'Nets', 19, 10, 3),(5, 'Knicks', NULL, 12, 8),(6, 'Celtics', 15, 1, 2);
We can view the table using:
SELECT*FROM athletes;
The output is:
+----+---------+--------+---------+----------+| id | team | points | assists | rebounds |+----+---------+--------+---------+----------+| 1 | Mavs | 22 | 4 | 3 || 2 | Kings | NULL | 5 | 13 || 3 | Lakers | 37 | 6 | 10 || 4 | Nets | 19 | 10 | 3 || 5 | Knicks | NULL | 12 | 8 || 6 | Celtics | 15 | 1 | 2 |+----+---------+--------+---------+----------+
Notice that the points column contains NULL values for the Kings and Knicks rows.
Suppose we want to create a new column called points_edited that replaces every NULL value with the word Zero.
We can use:
SELECT id, team, points,CASEWHEN points ISNULLTHEN'Zero'ELSE pointsENDAS points_editedFROM athletes;
The output is:
+----+---------+--------+---------------+| id | team | points | points_edited |+----+---------+--------+---------------+| 1 | Mavs | 22 | 22 || 2 | Kings | NULL | Zero || 3 | Lakers | 37 | 37 || 4 | Nets | 19 | 19 || 5 | Knicks | NULL | Zero || 6 | Celtics | 15 | 15 |+----+---------+--------+---------------+
The CASE expression checks each row individually.
For example:
Mavs → 22 → 22Kings → NULL → ZeroLakers → 37 → 37Nets → 19 → 19Knicks → NULL → ZeroCeltics → 15 → 15
Why Use IS NULL Instead of = NULL?
One of the most important rules when working with NULL in MySQL is that you should use:
ISNULL
rather than:
=NULL
For example, this is correct:
SELECT*FROM athletesWHERE points ISNULL;
This is not the correct way to test for NULL:
SELECT*FROM athletesWHERE points =NULL;
NULL represents the absence of a known value, so normal equality comparisons do not work as they do with ordinary values.
The same rule applies inside a CASE statement.
Use:
CASEWHEN points ISNULLTHEN'Zero'ELSE pointsEND
rather than:
CASEWHEN points =NULLTHEN'Zero'ELSE pointsEND
Using IS NOT NULL with CASE
You can also explicitly test whether a value is not NULL using IS NOT NULL.
For example:
SELECT id, team, points,CASEWHEN points ISNOTNULLTHEN'Available'ELSE'Missing'ENDAS points_statusFROM athletes;
The output would be:
+----+---------+--------+--------------+| id | team | points | points_status|+----+---------+--------+--------------+| 1 | Mavs | 22 | Available || 2 | Kings | NULL | Missing || 3 | Lakers | 37 | Available || 4 | Nets | 19 | Available || 5 | Knicks | NULL | Missing || 6 | Celtics | 15 | Available |+----+---------+--------+--------------+
This is particularly useful for creating data-quality flags.
Replacing NULL with a Numeric Value
In many analytical queries, you may want to replace a missing value with 0 rather than the string 'Zero'.
For example:
SELECT id, team, points,CASEWHEN points ISNULLTHEN0ELSE pointsENDAS points_editedFROM athletes;
The result is:
+----+---------+--------+---------------+| id | team | points | points_edited |+----+---------+--------+---------------+| 1 | Mavs | 22 | 22 || 2 | Kings | NULL | 0 || 3 | Lakers | 37 | 37 || 4 | Nets | 19 | 19 || 5 | Knicks | NULL | 0 || 6 | Celtics | 15 | 15 |+----+---------+--------+---------------+
This version is generally more appropriate when points_edited will be used in mathematical calculations.
Replacing NULL with a Text Value
If the column is being prepared for display in a report, you might instead use a text value such as:
SELECT id, team,CASEWHEN points ISNULLTHEN'Not Available'ELSECAST(points ASCHAR)ENDAS points_displayFROM athletes;
Possible output:
+----+---------+---------------+| id | team | points_display|+----+---------+---------------+| 1 | Mavs | 22 || 2 | Kings | Not Available || 3 | Lakers | 37 || 4 | Nets | 19 || 5 | Knicks | Not Available || 6 | Celtics | 15 |+----+---------+---------------+
The CAST() is useful because the CASE expression is now returning text in every branch.
CASE with Multiple NULL Conditions
You can check more than one column for NULL.
Suppose we want to determine whether an athlete has missing information in either points or assists:
SELECT id, team, points, assists,CASEWHEN points ISNULLAND assists ISNULLTHEN'Both Missing'WHEN points ISNULLTHEN'Points Missing'WHEN assists ISNULLTHEN'Assists Missing'ELSE'Complete'ENDAS data_statusFROM athletes;
Here, the AND operator requires both columns to be NULL.
You can also use OR when either column being NULL should trigger the condition:
SELECT id, team, points, assists,CASEWHEN points ISNULLOR assists ISNULLTHEN'Incomplete'ELSE'Complete'ENDAS data_statusFROM athletes;
CASE with NULL and Other Conditions
You can combine IS NULL with ordinary conditions.
For example, suppose we want to categorize athletes based on their points:
SELECT id, team, points,CASEWHEN points ISNULLTHEN'Missing'WHEN points >=30THEN'High'WHEN points >=20THEN'Medium'ELSE'Low'ENDAS performanceFROM athletes;
The resulting logic is:
NULL→Missing- 30 or more →
High - 20–29 →
Medium - Less than 20 →
Low
The order is important. The NULL condition is checked first, followed by the numerical conditions.
Using CASE to Create Data-Quality Categories
CASE with IS NULL is particularly useful when evaluating data quality.
For example:
SELECT id, team, points, assists, rebounds,CASEWHEN points ISNULLOR assists ISNULLOR rebounds ISNULLTHEN'Incomplete'ELSE'Complete'ENDAS record_statusFROM athletes;
This creates a simple data-quality indicator.
A row is classified as Incomplete if any of the three statistics is missing.
This approach can be useful in:
- Data quality dashboards
- ETL pipelines
- Data warehouse validation
- Customer databases
- Financial reporting
- Healthcare analytics
- Business intelligence
- Machine learning preprocessing
CASE vs COALESCE for NULL Values
If your only objective is to replace a NULL with another value, MySQL’s COALESCE() function is often simpler.
For example:
SELECT id, team, points, COALESCE(points, 0) AS points_editedFROM athletes;
This replaces NULL with 0.
The equivalent CASE expression is:
SELECT id, team, points,CASEWHEN points ISNULLTHEN0ELSE pointsENDAS points_editedFROM athletes;
Both approaches can produce the same result.
However, CASE becomes more useful when you need multiple business rules.
For example:
CASEWHEN points ISNULLTHEN'Missing'WHEN points >=30THEN'High'WHEN points >=20THEN'Medium'ELSE'Low'END
This is something COALESCE() alone cannot accomplish.
CASE vs IFNULL
MySQL also provides IFNULL():
SELECT id, team, IFNULL(points, 0) AS points_editedFROM athletes;
This is another concise way to replace NULL.
For simple replacement:
COALESCE(points, 0)
or:
IFNULL(points, 0)
may be easier to read.
For conditional logic involving multiple rules, CASE is generally more flexible.
Using CASE in an UPDATE Statement
A CASE expression can also be used to permanently update missing values.
For example:
UPDATE athletesSET points =CASEWHEN points ISNULLTHEN0ELSE pointsEND;
After this update, the NULL values in points will be replaced with 0.
However, be careful when modifying production data. It is often better to first preview the result:
SELECT id, team, points,CASEWHEN points ISNULLTHEN0ELSE pointsENDAS points_editedFROM athletes;
This lets you verify the transformation before updating the underlying table.
Complete MySQL Example
The following example can be copied and executed directly:
CREATETABLE athletes ( id INTPRIMARYKEY, team VARCHAR(50), points INT, assists INT, rebounds INT);INSERTINTO athletes VALUES(1, 'Mavs', 22, 4, 3),(2, 'Kings', NULL, 5, 13),(3, 'Lakers', 37, 6, 10),(4, 'Nets', 19, 10, 3),(5, 'Knicks', NULL, 12, 8),(6, 'Celtics', 15, 1, 2);SELECT id, team, points,CASEWHEN points ISNULLTHEN'Zero'ELSECAST(points ASCHAR)ENDAS points_editedFROM athletes;
The query checks each value in points and creates a new column named points_edited.
Common Mistakes When Checking NULL with CASE
Using = NULL
Avoid:
WHEN points =NULLTHEN'Zero'
Use:
WHEN points ISNULLTHEN'Zero'
Forgetting ELSE
If no WHEN condition is satisfied and there is no ELSE, the CASE expression returns NULL.
For predictable results, consider:
CASEWHEN points ISNULLTHEN0ELSE pointsEND
Mixing Numbers and Text Without Considering the Result Type
This expression mixes a string and a number:
CASEWHEN points ISNULLTHEN'Zero'ELSE pointsEND
For display purposes, this may be appropriate. But if the resulting column needs to be used for numerical calculations, returning 0 is generally more appropriate:
CASEWHEN points ISNULLTHEN0ELSE pointsEND
Confusing NULL with Zero
NULL and 0 are not the same.
NULL generally means that the value is missing or unknown, while 0 is an actual numerical value.
For example:
points = NULL → points are unknown or missingpoints = 0 → points are explicitly zero
Replacing NULL with 0 should therefore be a deliberate data-cleaning decision.
Frequently Asked Questions
How do I check for NULL inside a MySQL CASE statement?
Use IS NULL:
CASEWHEN points ISNULLTHEN0ELSE pointsEND
How do I check for NOT NULL in CASE?
Use IS NOT NULL:
CASEWHEN points ISNOTNULLTHEN'Available'ELSE'Missing'END
Can I replace NULL with zero using CASE?
Yes:
CASEWHEN points ISNULLTHEN0ELSE pointsEND
Is NULL the same as zero in MySQL?
No. NULL represents a missing or unknown value, whereas 0 is a valid numeric value.
Should I use CASE or COALESCE?
For simple NULL replacement, COALESCE() is concise:
COALESCE(points, 0)
For multiple conditional rules, CASE is more flexible.
Can CASE check multiple columns for NULL?
Yes. For example:
CASEWHEN points ISNULLOR assists ISNULLTHEN'Incomplete'ELSE'Complete'END
Conclusion
The MySQL CASE statement provides a flexible way to detect and handle NULL values while transforming query results. The key syntax to remember is:
CASEWHEN column_name ISNULLTHEN replacement_valueELSE column_nameEND
For example:
SELECT id, team, points,CASEWHEN points ISNULLTHEN0ELSE pointsENDAS points_editedFROM athletes;
When checking for missing values, always use IS NULL or IS NOT NULL rather than = NULL or <> NULL.
For simple replacement of missing values, COALESCE() and IFNULL() are convenient alternatives. However, when NULL handling needs to be combined with additional business rules, ranges, categories, or multiple conditions, CASE is often the better choice.
