NULL & Three-Valued Logic | Relational Databases

In this chapter

We'll meet SQL's strangest data point — NULL — and the three-valued logic (TRUE, FALSE, UNKNOWN) that makes it behave nothing like zero or an empty value.

12–15 min

The Problem in Real Life

Sarah adds an eleventh customer to the books — new this week, no phone number given yet. She leaves the Phone box blank and moves on, not thinking twice about it.

Later, Mike asks how many customers don't have a phone on file. Sarah writes what seems like the obvious query — WHERE Phone = NULL — and gets back zero rows. Not one. Even though she just typed a blank phone number herself, minutes ago.

Zero? That can't be right — I just left one blank.

Sarah

Blank, Zero, and Unknown Aren't the Same Thing

Blank isn't the same as missing

A phone number left empty isn't zero and isn't an empty string — it's an entirely separate concept SQL calls NULL.

"= NULL" fails silently

WHERE Phone = NULL never errors and never matches anything, even when rows genuinely have no phone at all.

IS NULL is a different kind of question

Testing for a missing value needs its own dedicated syntax, not the equals sign used everywhere else.

Every report inherits this problem

Counting, summing, or averaging a column with missing values behaves differently than most people expect on the first try.

What Is NULL, Really?

A blank phone number isn't the same as the phone number "0", and it isn't the same as an empty bit of text either. SQL has a completely separate idea for a value that's genuinely missing, unmeasured, or not yet known: NULL. It isn't a value at all — it's the absence of one.

That difference sounds small until it collides with the equals sign. Phone = NULL doesn't mean "find the rows with no phone." It's a question SQL genuinely can't answer yes or no to, because comparing anything to "unknown" is itself unknown. This is called three-valued logic: every condition in SQL actually evaluates to TRUE, FALSE, or UNKNOWN — not just the two outcomes most people expect.

Table — Customers, After Sana Joins With No Phone Yet

CustomerID	Name	Phone	BalanceDue
101	Priya	555-0142	140
102	Alex	555-0198	0
103	Maria	555-0173	75
111	Sana	NULL	0

"Phone" is a column — every row has this factThis whole line is a row — one record

That's a literal NULL sitting in the Phone column for Sana — not blank text, not a zero, and not the word "NULL" as a string. It's an entirely different kind of value.

NULL means a value is missing or unknown — never zero, never an empty string, and never even equal to another NULL. That's exactly why Phone = NULL can't work as a comparison: WHERE only keeps rows where a condition is TRUE, and comparing anything to NULL evaluates to UNKNOWN, not TRUE or FALSE — so every row gets silently filtered out.

The Query That Returns Nothing

SELECT *
FROM Customers
WHERE Phone = NULL;

This looks like it should find every customer with no phone on file — including Sana, added moments ago. It returns zero rows, every time. = NULL isn't a real comparison; it's always UNKNOWN, so WHERE throws every row away, silently.

IS NULL is a dedicated test, not a comparison — it's the only correct way to ask whether a value is missing.

The Correct Way to Ask

SELECT *
FROM Customers
WHERE Phone IS NULL;

IS NULL is a dedicated test, not a comparison — it's the only way SQL correctly recognizes a missing value. This is the query that actually finds Sana.

IS NOT NULL is the mirror test — it finds every row where a real value is actually present.

Finding Everyone Who Does Have One

SELECT *
FROM Customers
WHERE Phone IS NOT NULL;

The mirror image of IS NULL — every customer with a real phone number on file, Sana correctly excluded.

COALESCE returns the first non-NULL value from a list of arguments — a common way to display a fallback value in place of a missing one, without changing what's actually stored.

Filling In a Default With COALESCE

SELECT Name, COALESCE(Phone, 'No phone on file') AS Phone
FROM Customers
ORDER BY Name;

COALESCE swaps in a fallback value only where the real one is NULL — Sana shows "No phone on file" in this result, but her actual row in the table is completely untouched.

Look at what actually changed between the four queries: = NULL is always UNKNOWN, no matter what's really stored in the column — and it doesn't even raise an error, which is exactly what makes this mistake so easy to write and so easy to miss. IS NULL and IS NOT NULL are the only conditions built specifically to handle it, and COALESCE is how a missing value gets a friendly stand-in without changing anything in the actual table.

Three-valued logic doesn't stop at simple comparisons — it means AND and OR have to account for UNKNOWN too, and it changes how GreenMart's upcoming reports have to treat missing data. NULL isn't a bug to work around once and forget; it's a permanent third answer SQL is always prepared to give.

Key Takeaway

NULL means "unknown," never zero or empty — and any comparison against it, including = and <>, evaluates to UNKNOWN rather than TRUE or FALSE. IS NULL and IS NOT NULL are the only reliable way to test for it.

Why This Matters

Every report GreenMart runs from here forward has to account for missing data somewhere — a customer with no phone yet, a sale with no date recorded, a product nobody has priced. The aggregate functions in the very next chapter behave differently around NULL than most people expect on the first try, and understanding three-valued logic now is what makes that behavior predictable instead of mysterious.

GreenMart's database can finally tell the difference between zero, blank, and genuinely unknown. Next, Mike wants numbers — not rows, actual totals and counts — which means meeting SQL's aggregate functions.