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 |
--- +------------------+---------------------+
--- | [email protected] | 2025-06-16 10:47:25 |
--- | [email protected] | NULL |
--- | [email protected] | NULL |
--- | [email protected] | NULL |
--- | [email protected] | 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 |
--- +------------------+---------------------+
--- | [email protected] | NULL |
--- | [email protected] | NULL |
--- | [email protected] | 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.
Top comments (0)