toolhq.io

All posts
5 min readby Jameel Haider

SQL NULL and three-valued logic

A query that looks obviously correct returns no rows, and the cause is almost always NULL. In SQL, NULL is not zero and not an empty string. It means the value is unknown, and that single idea changes how comparisons, conditions, and aggregates behave. Once you accept that NULL means unknown, the surprising results start to make sense.

NULL means unknown

Think of NULL as a placeholder for a value that exists but is not recorded, or that does not apply. Because the value is unknown, SQL cannot answer questions about it with a plain yes or no. Is an unknown salary greater than 50000? The honest answer is unknown, not false. That honesty is the root of everything below.

Why = NULL never matches

The natural instinct is to write WHERE salary = NULL, and it always returns nothing. Comparing anything to an unknown yields unknown, not true, so the row is never selected. This is the single most common NULL bug.

You must use the dedicated operators:

-- wrong: returns no rows, ever
SELECT * FROM employees WHERE manager_id = NULL;

-- right
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

IS NULL and IS NOT NULL are the only tests that give a true or false answer about a NULL.

Three-valued logic

SQL conditions are not simply true or false. They can be true, false, or unknown. A WHERE clause only keeps a row when the condition is true, so an unknown result drops the row just like a false one would. That distinction matters with AND and OR:

  • true AND unknown is unknown (row dropped).
  • false AND unknown is false.
  • true OR unknown is true (row kept).
  • unknown OR unknown is unknown.

A particularly sharp edge is NOT IN with a NULL in the list. If the list contains a NULL, NOT IN can evaluate to unknown for every row and return nothing, which surprises people who expected the non-matching rows.

Aggregates ignore NULL

Aggregate functions quietly skip NULLs, and this is usually what you want but worth knowing:

  • COUNT(*) counts rows, including those with NULLs.
  • COUNT(column) counts only rows where that column is not NULL.
  • SUM, AVG, MIN, and MAX ignore NULLs. Note that AVG divides by the count of non-null values, not the total row count, so nulls do not drag the average toward zero.

COALESCE supplies a fallback

When you want a concrete value instead of NULL, COALESCE returns the first non-null argument:

SELECT name, COALESCE(nickname, name) AS display_name
FROM users;

This is the clean way to substitute a default in a result set or before a calculation, rather than scattering CASE expressions through a query.

Keep queries readable

As soon as NULL handling, joins, and aggregates pile up, a query gets hard to scan, and a messy query hides exactly these bugs. The SQL formatter reindents and aligns a query so the WHERE conditions and COALESCE calls stand out, and it runs entirely in your browser so your query text never leaves your device. For related reading, SQL joins explained covers another place NULLs appear, in outer joins.

The takeaway: treat NULL as unknown, reach for IS NULL instead of =, watch NOT IN and AND combinations, and use COALESCE when you need a real value.