What exactly is NULL in SQL, and why does it behave so differently from every other value?
NULL does not mean zero and it does not mean an empty string. It means that the value is not known.
That is the whole reason it behaves differently from every other value. The database does not know what the value is, so it cannot tell you whether it matches a condition. When you compare something with NULL, the answer is not true and it is not false. The answer is a third thing, which is called unknown.
CREATE TABLE staff (
id INTEGER PRIMARY KEY,
name VARCHAR(80) NOT NULL,
manager_id INTEGER REFERENCES staff(id)
);
INSERT INTO staff (id, name, manager_id) VALUES
(1, 'Ada', NULL),
(2, 'Grace', 1),
(3, 'Alan', 2);
-- This returns no rows, because every comparison with NULL is unknown.
SELECT * FROM staff WHERE manager_id = NULL;
-- This is the right way to ask for it.
SELECT * FROM staff WHERE manager_id IS NULL;
So what are the rules that follow from that?
The first one is that you never write = NULL. You write IS NULL and IS NOT NULL. The reason the equality does not work is that the engine compares the value with the NULL and gets the answer unknown, and a row is only returned when the answer is definitely true.
The second one is that NULL is never equal to anything, including another NULL. The expression NULL = NULL is unknown, not true. If you need to compare two things that might both be missing, you use the operator IS NOT DISTINCT FROM, which treats two missing values as the same.
The third one is that the aggregates skip the missing values. The function COUNT(column) counts the rows where the column has a value, while COUNT(*) counts every row. The functions SUM, AVG, MIN and MAX all ignore the missing values.
The fourth one is that any calculation with a missing value gives a missing value. So a column of numbers where one row is NULL has a sum that is not the total of the other rows, because the whole sum is NULL.
And the fifth one is that NOT NULL in the column definition is the real protection. It says that the column must always have a value, and the engine refuses the row if it does not. That is much better than finding the problem later in a report.