What’s the Difference Between IS NULL and = NULL in a MySQL WHERE Clause?

The IS NULL operator will return 1 (TRUE) or 0 (FALSE), while = NULL will always return NULL.

Therefore, in a WHERE clause, IS NULL is the correct way to check if a column is NULL, while = NULL will not return any rows because it never produces a TRUE value.

See the users table below:

SELECT email, deleted_at
FROM users;

--- +------------------+---------------------+
--- | email            | deleted_at          |
--- +------------------+---------------------+
--- | [e-mail]  | 2025-06-16 10:47:25 |
--- | [e-mail]  | NULL                |
--- | [e-mail] | NULL                |
--- | [e-mail]  | NULL                |
--- | [e-mail]  | 2025-06-20 04:53:25 |
--- +------------------+---------------------+
--- 5 rows in set (0.00 sec)

Let's query all users where deleted_at is NULL using the IS NULL operator.

SELECT email, deleted_at
FROM users
WHERE deleted_at IS NULL;

--- +------------------+---------------------+
--- | email            | deleted_at          |
--- +------------------+---------------------+
--- | [e-mail]  | NULL                |
--- | [e-mail] | NULL                |
--- | [e-mail]  | NULL                |
--- +------------------+---------------------+
--- 3 rows in set (0.00 sec)

Now, let's query all users where deleted_at is NULL using the = NULL operator.

SELECT email, deleted_at
FROM users
WHERE deleted_at = NULL;

--- Empty set (0.03 sec)

From the results above, IS NULL worked as expected to query the users table based on the NULL condition, while = NULL didn't return any rows.

The same rule applies to IS NOT NULL and != NULL as well.

Comments

[DevOps Fundamental]

Insightful and well-written as always!

[Ibrahim]

Thank You