How do I check for Null in a CASE Statement using MySQL?

---

You can use the following syntax in MySQL to check for null values when using a CASE statement:

SELECT id, team, points,
  (CASE 
    WHEN points IS NULL
    THEN 'Zero'
    ELSE points
  END) AS point_edited
FROM athletes;

This particular example checks if the value in the points column is null.

If the value in the points column is null, then a string value of ‘zero’ is returned.

Otherwise the original value from the points column is returned.

The following example shows how to use this syntax in practice.

Example: How to Check for Null in CASE Statement in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT,
  points INT,
  assists INT,
  rebounds INT
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 4, 3);
INSERT INTO athletes VALUES (0002, 'Kings', NULL, 5, 13);
INSERT INTO athletes VALUES (0003, 'Lakers', 37, 6, 10);
INSERT INTO athletes VALUES (0004, 'Nets', 19, 10, 3);
INSERT INTO athletes VALUES (0005, 'Knicks', NULL, 12, 8);
INSERT INTO athletes VALUES (0006, 'Celtics', 15, 1, 2);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----+---------+--------+---------+----------+
| 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 |
+----+---------+--------+---------+----------+

Suppose that we would like to select the id, team and points columns while using a CASE statement to create a new column named points_edited that contains the values from the points column with a value of ‘zero’ for each row that contains NULL.

We can use the following syntax to do so:

SELECT id, team, points,
  (CASE 
    WHEN points IS NULL
    THEN 'Zero'
    ELSE points
  END) AS points_edited
FROM athletes;

Output:

+----+---------+--------+---------------+
| 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            |
+----+---------+--------+---------------+

Notice that the points_edited column contains a value of ‘Zero’ in each row where the points column contained a NULL value.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest