IS NULL and IS NOT NULL

NULL is checked with dedicated predicates

We already know that NULL represents the absence of a known value. That is why it cannot be correctly searched as an ordinary value with = or <>.

SELECT id, name
FROM users
WHERE email IS NULL;

IS NULL returns rows where the value is missing. The opposite check is written as IS NOT NULL.

SELECT id, name
FROM users
WHERE email IS NOT NULL;

Why = NULL does not behave as expected

A comparison assumes two known values. If one side is unknown, SQL cannot say that they are equal or not equal in the ordinary way. That is why email = NULL is not a substitute for email IS NULL.