SQL.How does NULL differ from 0, an empty string, or FALSE in SQL?

Short, interview-ready answer

NULL means “unknown or not applicable”, while 0, empty string (''), and FALSE are known, concrete values.
NULL propagates through expressions and comparisons, whereas the others behave like normal values.

---

Core semantic difference

NULL

• Represents absence of a value
• Not equal to anything — not even another NULL
• Causes expressions to evaluate to UNKNOWN

0

• A numeric value
• Fully comparable and calculable

'' (empty string)

• A string of length 0
• Still a valid value

FALSE

• A boolean value
• Known and comparable

---

Comparison behavior (this is the trap)

NULL = NULL        → UNKNOWN
NULL <> NULL       → UNKNOWN
NULL = 0           → UNKNOWN
NULL = ''          → UNKNOWN

but

0 = 0              → TRUE
'' = ''            → TRUE
FALSE = FALSE      → TRUE

Consequence

WHERE column = NULL

Correct

WHERE column IS NULL

Logical expressions (three-valued logic)

TRUE  AND NULL  → UNKNOWN
FALSE AND NULL  → FALSE
TRUE  OR  NULL  → TRUE
FALSE OR  NULL  → UNKNOWN
NOT NULL        → UNKNOWN

WHERE keeps only TRUE, so UNKNOWN rows are filtered out.

Aggregations and NULLs

COUNT(column)   -- ignores NULL
COUNT(*)        -- counts rows
SUM(column)     -- ignores NULL

But:

AVG(NULL) → NULL

This surprises many candidates.

NULL in constraints

• UNIQUE allows multiple NULLs (in most DBs)
• NOT NULL enforces presence
• CHECK (col > 0) → fails if col is NULL (because result is UNKNOWN)

---

Why confusing NULL with 0/”/FALSE is dangerous

Bad design example

balance INT DEFAULT 0

Does 0 mean:

• truly zero?
• unknown?
• not loaded yet?

Correct modeling:

• Use NULL for “unknown”
• Use real values for real meaning

Interview-ready final answer (clean)

“NULL represents the absence of a value, not a value itself, while 0, empty string, and FALSE are concrete values.
Comparisons with NULL evaluate to UNKNOWN due to SQL’s three-valued logic, which is why IS NULL must be used instead of =.
Misusing NULL leads to subtle filtering and aggregation bugs.”

---

Senior red flags 🚩

If a candidate says:

• “NULL is just another value”
• “NULL equals NULL”
• “I use 0 instead of NULL”