In MySQL, the ISNULL() function enables us to check whether a value is null or not. If it’s null, then 1 is returned, otherwise 0 is returned.

Syntax

ISNULL(expr)

Example

Here’s a simple example to demonstrate:

SELECT ISNULL( null );

Result:

1

In this case, the expression is null and so the output is 1.

Here it is with a non-null value:

SELECT ISNULL( 'Cake' );

Result:

0

This time the result is 0 (because the expression is not null).

Expressions

ISNULL() checks the result of any expression. So if we do the following, for example:

SELECT ISNULL( 3 / 0 );

We get this:

1

So although we didn’t specify null as the argument, the result of the expression is null, and so ISNULL() returns 1.

This can be demonstrated more clearly with the next example:

SELECT 
    3 / 0,
    ISNULL( 3 / 0 );

Result:

+-------+-----------------+
| 3 / 0 | ISNULL( 3 / 0 ) |
+-------+-----------------+
|  NULL |               1 |
+-------+-----------------+
1 row in set, 2 warnings (0.00 sec)

In the first column we can see that the calculation itself resulted in a null value, and so the second column returned 1.

While we’re at it, here are the warnings:

SHOW WARNINGS

Result:

+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
| Warning | 1365 | Division by 0 |
+---------+------+---------------+

We’re warned that we tried to divide by zero (which results in a null value).

Database Example

Suppose we have a database table like this:

SELECT * FROM Pets;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     3 |         2 |       2 | Scratch | 2018-10-01 |
|     4 |         3 |       3 | Wag     | 2020-03-15 |
|     5 |         1 |       1 | Tweet   | 2020-11-28 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
|     7 |         3 |       2 | Bark    | NULL       |
|     8 |         2 |       4 | Meow    | NULL       |
|     9 |         3 |       1 | Woof    | 2020-10-03 |
|    10 |         4 |       5 | Ears    | 2022-01-11 |
+-------+-----------+---------+---------+------------+

Here’s a query that uses the ISNULL() function against that table:

SELECT
    PetId,
    PetName,
    ISNULL( DOB ) AS "DOB is Missing?"
FROM Pets;

Result:

+-------+---------+-----------------+
| PetId | PetName | DOB is Missing? |
+-------+---------+-----------------+
|     1 | Fluffy  |               0 |
|     2 | Fetch   |               0 |
|     3 | Scratch |               0 |
|     4 | Wag     |               0 |
|     5 | Tweet   |               0 |
|     6 | Fluffy  |               0 |
|     7 | Bark    |               1 |
|     8 | Meow    |               1 |
|     9 | Woof    |               0 |
|    10 | Ears    |               0 |
+-------+---------+-----------------+

In that case, we created a column that displays 1 for all pets that don’t have anything in their DOB column.

Let’s say we want to return all pets that don’t have a date of birth listed (i.e. their DOB field is null). In this case, we can do the following:

SELECT * FROM Pets
WHERE ISNULL(DOB) = 1;

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
|     8 |         2 |       4 | Meow    | NULL |
+-------+-----------+---------+---------+------+

That yields the same result as doing the following:

SELECT * FROM Pets
WHERE DOB IS NULL;

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
|     8 |         2 |       4 | Meow    | NULL |
+-------+-----------+---------+---------+------+