MySQL IS NULL

In MySQL, NULL is a special value, which means nothing. It is neither blank nor 0.

The IS NULL operator is used to test whether a value is NULL or not. If the specified value is NULL, it returns 1, otherwise 0.

IS NULL syntax

The IS NULL operator is a unary comparison operator and only requires one operand. The syntax of the operator is:

expression IS NULL
expression IS NOT NULL

Here:

• expression can be a column name, a value, or a expression.
• IS NOT NULL is the negation operator of IS NULL.

You can use IS NULL and IS NOT NULL in a SELECT statement or WHERE clause.

IS NULL algorithm

When the operand is NULL, the IS NULL operator returns 1, otherwise 0.

SELECT
    NULL IS NULL,
    0 IS NULL,
    1 IS NULL,
    (NULL IN (NULL)) IS NULL,
    (1+1) IS NULL;

+--------------+-----------+-----------+--------------------------+---------------+
| NULL IS NULL | 0 IS NULL | 1 IS NULL | (NULL IN (NULL)) IS NULL | (1+1) IS NULL |
+--------------+-----------+-----------+--------------------------+---------------+
|            1 |         0 |         0 |                        1 |             0 |
+--------------+-----------+-----------+--------------------------+---------------+

IS NOT NULL is the negation operator of IS NULL. If the operand is not NULL, the IS NOT NULL operator returns 1, otherwise 0.

SELECT
    NULL IS NOT NULL,
    0 IS NOT NULL,
    1 IS NOT NULL;

+------------------+---------------+---------------+
| NULL IS NOT NULL | 0 IS NOT NULL | 1 IS NOT NULL |
+------------------+---------------+---------------+
|                0 |             1 |             1 |
+------------------+---------------+---------------+

IS NULL examples

In the following examples, we use staff table in Sakila sample database as a demonstration.

The following SQL statement is used to find all staff that did not set a password.

SELECT first_name, last_name, password
FROM staff
WHERE password IS NULL;

+------------+-----------+----------+
| first_name | last_name | password |
+------------+-----------+----------+
| Jon        | Stephens  | NULL     |
+------------+-----------+----------+

Similarly, if you want to find all staff that have set a password, please use the following SQL statement.

SELECT first_name, last_name, password
FROM staff
WHERE password IS NOT NULL;

+------------+-----------+------------------------------------------+
| first_name | last_name | password                                 |
+------------+-----------+------------------------------------------+
| Mike       | Hillyer   | 8cb2237d0679ca88db6464eac60da96345513964 |
+------------+-----------+------------------------------------------+

Conclusion

In this article, you learned the syntax and usage of MySQL IS NULL and IS NOT NULL comparison operators. The main points of this article are as follows:

• Both IS NULL and IS NOT NULL are unary comparison operators.
• IS NULL is used to test whether a value is NULL or not.
• IS NOT NULL is the negation operation of IS NULL.
• NULL IS NULL returns 1.