Why does NULL = NULL evaluate to unknown instead of true?
Because a missing value does not mean "nothing". It means "not known", and the engine cannot promise that two things that are both unknown are the same.
Here is the rule. When you compare a value with a missing value, the answer is not true and it is not false. The answer is a third thing, which is called unknown. And a row only comes back in the answer when the condition is definitely true, so a comparison with a missing value never matches anything.
That is why the expression NULL = NULL gives unknown rather than true. If you knew both values and they were the same, then true would be right. But you do not know either of them, so the engine refuses to say.
CREATE TABLE people (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
preferred_name VARCHAR(120)
);
INSERT INTO people (id, name, preferred_name) VALUES
(1, 'Ada Lovelace', 'Ada'),
(2, 'Grace Hopper', NULL);
-- This returns no rows at all.
SELECT * FROM people WHERE preferred_name = NULL;
-- This returns the second row.
SELECT * FROM people WHERE preferred_name IS NULL;
-- Two missing values are not equal to each other either.
SELECT 'a' = NULL AS first, NULL = NULL AS second;
So there is a third value in every comparison, and a condition is only true when it is definitely true. That is why the operator IS NULL exists, and why it is the only way to ask about a missing value.
The next question in this topic asks how to compare two columns when either of them might be missing.