At some point, almost every developer writes a query like WHERE email = NULL and gets zero rows back. They check the table, and they can clearly see rows where the email is empty. They run it again, but still nothing. They switch to WHERE email IS NULL, and suddenly the rows appear. The query looks almost identical. The difference feels like a trick. It is not a trick — it is logic, and once you understand it, it changes how you read every query you will ever write.
NULL Does Not Mean Empty
The first thing to understand is what NULL actually represents. NULL is not an empty string. It is not zero. It is not false. NULL means the value is unknown. The column does not have a value — not because the value is blank, but because the value has not been established.
This distinction matters because of what happens when you try to compare something unknown with something else. If you do not know what is in a box, can you say the box is equal to another box you also cannot see inside? You cannot say yes, and you cannot say no. The only honest answer is that you do not know.
That unknown answer has a name in SQL. It is called NULL. Or more precisely, the result of any comparison involving NULL is itself NULL — not true, not false, but unknown.
Three Answers Instead of Two
Most programming languages deal in booleans — true or false, yes or no. SQL deals in three possible answers: true, false, and unknown. This is called three-valued logic, and it is not an accident or a quirk. It is the foundation of how SQL handles missing information, and it is defined this way in the SQL standard.
When you write WHERE email = NULL, the database evaluates that expression for every row. For rows where email has a real value, the comparison between that value and NULL produces unknown. For rows where email is also NULL, the comparison between unknown and unknown also produces unknown. There is no row where this expression produces true, because nothing is ever equal to unknown — not even another unknown.
The WHERE clause has a simple rule that comes directly from the source code inside PostgreSQL's expression evaluator: if the result of a condition is true, the row is included. If the result is false or unknown, the row is excluded. NULL and false are treated identically by the filter. This is why WHERE email = NULL returns nothing — every single row evaluates to unknown, and unknown rows are thrown out.
IS NULL Is a Different Kind of Test
When you write WHERE email IS NULL, you are not doing a comparison at all. You are asking a structural question: Does this column have a value or not? PostgreSQL handles this with a completely different mechanism internally. At the level of the query tree, email = NULL and email IS NULL are different kinds of nodes. The equality check calls a function and passes the values as arguments. The IS NULL check looks directly at whether a value exists, without calling any comparison function at all.
This is also why strict functions — functions that return NULL if any of their inputs are NULL — behave the way they do. The equality operator is strict. If you pass NULL to it, it short-circuits immediately and returns NULL without even running. The IS NULL operator is not strict in this sense because it does not compare values. It is asking whether a value exists.
AND and OR Are Affected Too
The three-valued logic does not stop at comparisons. It flows through every logical operator. If you write WHERE email IS NULL AND name = 'Alice', and the name comparison returns true but the email IS NULL check returns false, the AND of true and false is false — the row is excluded. That part is normal.
But consider WHERE email = NULL OR name = 'Alice'. If email = NULL produces unknown and name = 'Alice' produces true, the OR of unknown and true is true — because at least one side is definitely true. The row is included. This surprises people. The unknown part did not kill the expression because the other side saved it.
The rule for OR is: if either side is true, the result is true regardless of the other side. The rule for AND is: if either side is false, the result is false regardless of the other side. NULL only propagates when it cannot be resolved by the other operand.
The Fix Is Simple
Always use IS NULL to test for missing values and IS NOT NULL to test for present values. If you are comparing two columns and want NULL values to be considered equal to each other — the way you might when doing a join or a deduplication — use IS NOT DISTINCT FROM instead of =. That operator treats two NULLs as matching, because it is specifically designed to handle the unknown case in a sensible way.
The behavior is not a bug, and it is not PostgreSQL being difficult. It is the correct implementation of a standard that models the real world honestly. Some values are simply not known, and pretending otherwise would produce results that quietly lie to you.