PrismitaLEARN · PRACTICE · GROW

What is NULL in SQL, and what is three-valued logic?

SQL Server T-SQL Intermediate 1–2 Yrs2–3 Yrs Conceptual 1 min read

Quick Answer

NULL means “unknown”, not zero or empty string. Any comparison with NULL yields UNKNOWN — so x = NULL is never true; you must use IS NULL / IS NOT NULL. SQL uses three-valued logic: TRUE, FALSE, UNKNOWN, and WHERE only keeps rows that are TRUE.

NULL represents an unknown value — it is not zero and not an empty string. Because it's unknown, any comparison involving it returns UNKNOWN rather than true or false, which is why column = NULL never matches and you have to write column IS NULL. SQL's logic is three-valued — true, false, unknown — and a WHERE clause only keeps the true rows. The practical gotchas are that aggregates skip nulls, and NOT IN with a subquery that contains a null returns no rows at all.

  • NULL = NULL → UNKNOWN; NULL <> 1 → UNKNOWN. WHERE and JOIN drop UNKNOWN rows.
  • Aggregates ignore NULL: AVG(col) divides by the count of non-null values; COUNT(col) ignores nulls, COUNT(*) doesn't.
  • NOT IN (subquery) returns nothing if the subquery has a NULL.
  • NULLs sort together (first in SQL Server's default ascending).
  • Handle with ISNULL / COALESCE, NULLIF, or IS [NOT] DISTINCT FROM semantics.
All SQL Server questions More on T-SQL