SQL NULL vs empty string vs zero

NULL Is Not Zero, Not an Empty String, and Not False
This single idea explains most NULL-related surprises: NULL represents an unknown or missing value — not zero, not an empty string, not boolean false. It's a placeholder meaning "there is no value here to compare," and that has real consequences for how comparisons behave.

SELECT * FROM Employees WHERE salary = NULL;   -- returns ZERO rows, always, for every row in the table
SELECT * FROM Employees WHERE salary IS NULL;  -- correctly returns Dave
SELECT * FROM Employees WHERE salary IS NOT NULL; -- everyone except Dave

salary = NULL doesn't mean "salary is unset" — it asks "is salary equal to this unknown value," and the honest answer to that question is always "unknown," never "yes." SQL has no way to spell "compare to NULL and get true" using =; IS NULL / IS NOT NULL are dedicated operators that exist precisely because equality can't do this job.

For more like this, visit: https://codeoath.in/blog/sql-null-joins-aggregates

Comments

[Baptiste Le Bouquin]

Worth adding that the same trap bites in the other direction: WHERE salary != 50000 also drops NULL rows, so a naive 'exclusion' filter quietly hides exactly the records people are hunting for. Three-valued logic punishes both = and != equally.

Our habit now is NOT NULL at schema time with a sentinel default wherever 'unknown' isn't a meaningful state, and keeping NULL strictly for genuinely unknown values. Most NULL-vs-empty-string confusion is a data model decision that was never made explicitly.