NULL & Three-Valued Logic — How Missing Values Change Your Results

The situation this lesson prepares you for

Last lesson you made your first empty cells. A LEFT JOIN went looking for a match, found none, and left the space blank — and you were told those blanks were the answer, not a gap.

Today somebody asks you something that sounds much easier: which of our orders have not shipped yet?

You have been writing WHERE since the WHERE, AND/OR/NOT, IN, BETWEEN lesson. The shipped date is the empty one. So you test the shipped date against nothing, run it, and get back zero rows — while 27 orders sit in that table unshipped, one of them since December 2022.

Nothing errors. Nothing warns you. The query is not broken; it is answering a different question from the one you think you asked.

That is the whole of this lesson, and it happens three times over: once where the result is empty and looks wrong, once where two correct reports quietly fail to add up, and once where a number lands on a slide and is simply false.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

The obvious query, and what it returns

SELECT count(*) AS "Unshipped"
FROM "Orders"
WHERE "ShippedDate" = NULL;

Zero. No error, no red text, no hint that anything went wrong.

If you sent that upstairs you would have reported "we are all caught up", and there would be two years of backlog underneath it.

Why: NULL is not a value

An empty cell does not hold anything. Not a zero, not blank text, not a special marker you can compare against. NULL is the database recording that nobody ever wrote a value there.

So "ShippedDate" = NULL puts an equals sign between two things, and neither of them is a value. The honest answer is not yes, and it is not no.

The objection everyone has at this point is a fair one: both cells are empty, so surely they match. They do not, and the reason is worth saying carefully. An equals sign compares two values, and neither cell has one — there is nothing in either of them to put side by side. Two empty cells are not a match; they are two cells with nothing in them. So the test cannot come back TRUE, and it cannot come back FALSE either.

A NULL is not equal to another NULL. It is not unequal to it either.

The third answer

Most languages give you true and false. SQL has a third, and it has had it since Codd's relational model: UNKNOWN.

And now the part that costs money:

WHERE keeps the rows whose test came back TRUE, and throws away everything else.

UNKNOWN goes in the same bin as FALSE. There is no third pile to go and look in, and no message telling you a pile existed. That single sentence explains every bug on this page.

IS NULL: the test that always answers

SELECT count(*) AS "Unshipped"
FROM "Orders"
WHERE "ShippedDate" IS NULL;

IS NULL is not a comparison. It asks a different question — is there anything in this cell at all? — and that question can always be answered, so it comes back TRUE or FALSE and never UNKNOWN.

Its partner is IS NOT NULL, for the rows that do have a value. Those two are the only correct way to test for a missing value. Never write = NULL or <> NULL: both run, both match nothing, and neither will ever tell you.

You have used IS NULL a few times already without being told why. The WHERE, AND/OR/NOT, IN, BETWEEN lesson asked you to park the two words for now; the COUNT, SUM, AVG, MIN, MAX lesson asked you to use them and trust them. Now you know what they are for — and why an equals sign was never going to work.

And here is the backlog itself:

SELECT "OrderID", "CustomerID", "OrderDate"
FROM "Orders"
WHERE "ShippedDate" IS NULL
ORDER BY "OrderDate"
LIMIT 5;

A count is not a report, though. Somebody has to be telephoned about those orders, and ALFKI is not a name — so bring last lesson's JOIN along and this becomes something you could actually send:

SELECT c."CompanyName", o."OrderID", o."OrderDate"
FROM "Orders" o
JOIN "Customers" c
  ON c."CustomerID" = o."CustomerID"
WHERE o."ShippedDate" IS NULL
ORDER BY o."OrderDate"
LIMIT 5;

Note where the IS NULL sits: in the WHERE, about the LEFT-hand table. That is the safe placement taught in the JOINs: INNER, LEFT, RIGHT lesson, and it is unaffected by everything on this page.

An empty string is not a blank

IS NULL answers. But there is a second thing people reach for when they go hunting for missing text, and it fails in a way that looks exactly like the query you just fixed.

Somebody wants the customers with no region, and writes this:

SELECT count(*) AS "= ''"
FROM "Customers"
WHERE "Region" = '';

Zero — and there are 21 customers with no region sitting in that table.

This is not the same bug as = NULL, even though it has the same shape. = NULL is UNKNOWN, so the WHERE discards the row. = '' is a perfectly ordinary comparison that comes back FALSE. An empty string is a value: somebody wrote nothing down, deliberately, and the database stored that. A NULL is no value at all — nobody wrote anything. The database will say so if you ask it directly:

And the opposite test is the worse one, because it looks safe:

Ask for every region that is not an empty string and you would expect all 25 customers back — not one of them is an empty string. You get 4. The 21 blanks cannot answer that comparison either, so the WHERE throws them away exactly as before.

The expensive version: two reports that do not add up

The query above was the cheap mistake, because an empty result at least looks wrong. Here is the one that does not.

Your manager wants two numbers for the year-end review: how many orders shipped on or after the first of January, and how many went out before that.

Those two conditions cover every date there is. Anything is either on one side of the first of January or the other — there is no third option. Hold on to that, because it is about to turn out to be false.

SELECT count(*) AS "Orders"
FROM "Orders"
WHERE "ShippedDate" >= DATE '2024-01-01';

SELECT count(*) AS "Orders"
FROM "Orders"
WHERE "ShippedDate" < DATE '2024-01-01';

Both numbers are correct. Both queries are ordinary. And 18 + 34 is 52, out of 79 orders.

The 27 missing from both reports are the same 27 you counted a moment ago. An order that never shipped has no date to compare, so it is not on or after January — and it is not before January either. Both tests came back UNKNOWN. Both reports discarded it. Neither mentioned it.

A condition and its opposite do not divide a table in two. They divide it in three, and the third part is invisible.

NOT does not rescue you

The natural next move is to negate the test instead. It does not help:

SELECT count(*) AS "Orders"
FROM "Orders"
WHERE NOT (
  "ShippedDate" >= DATE '2024-01-01'
);

The same 34. Not one of the 27 came back.

NOT has only two things to turn over: it flips TRUE to FALSE and FALSE to TRUE. Hand it UNKNOWN and it hands UNKNOWN straight back — the opposite of "I do not know" is still "I do not know" — and WHERE discards it exactly as before.

AND and OR, and the one cell that saves you

AND and OR do have an escape hatch. If one side already settles the answer, the missing value cannot change it, and the database says so:

That first row is the fix. Bolt an OR onto the filter and the empty rows have a way back in:

SELECT count(*) AS "Orders"
FROM "Orders"
WHERE "ShippedDate" < DATE '2024-01-01'
   OR "ShippedDate" IS NULL;

61 + 18 = 79. The books balance, and the only thing that changed was saying out loud what should happen to the empty rows.

The rule worth writing down

Before you filter a column, ask whether it can be empty. If it can, your condition and its opposite do not add up to the whole table — so decide which side the empty rows belong on, and write it in.

Note what you do not get if you forget: not an error. A total that is short.

An ON is a comparison, so a join drops them too

Everything so far has been about WHERE. A join's ON is the same kind of test — a condition that has to come back TRUE — so it has the same hole in it. This one is harder to spot, because the query does not look empty. It looks finished.

Every customer has a region. Every order has a ship region. So: each customer, beside the orders that shipped to their own region.

SELECT count(*) AS "Rows"
FROM "Customers" c
JOIN "Orders" o ON c."Region" = o."ShipRegion";

Not one row. No error, no warning — and a count(*) of zero is a number, so it goes into a report looking like an answer.

The reason is the sentence from the top of this page, met in a new clause. 21 of the 25 customers have no region and all 79 orders have no ship region, so almost every pair the join tries is NULL = NULL — UNKNOWN — and the ON discards it exactly as a WHERE would. Not one blank recognises another.

You can prove that is what happened, rather than "there are genuinely no matches", with the operator from further down this page:

SELECT count(*)
FROM "Customers" c
JOIN "Orders" o
  ON c."Region" IS NOT DISTINCT FROM o."ShipRegion";

IS NOT DISTINCT FROM does treat blank as equal to blank, and it returns 21 x 79 = 1659 rows. Those pairs were always in the data. The plain join was dropping them, not failing to find them.

It spreads into your output too

So far a missing value has been removing rows. Now it removes something you did not know was at risk.

Back in the Aliases, Expressions & DISTINCT lesson you glued columns together with || and were warned about one thing for later. This is later. Mailing labels — city and region on one line:

SELECT "City", "Region", "City" || ', ' || "Region" AS "Label"
FROM "Customers"
ORDER BY "CustomerID"
LIMIT 3;

The city is right there on every row. The label is gone — not the region part of it, the whole line.

Anything combined with nothing is nothing. One empty piece and the entire expression collapses. The same is true of arithmetic: "Freight" + NULL is NULL, not "Freight". Twenty-one of the twenty-five customers here have no region, so twenty-one labels vanish.

COALESCE: put something there

SELECT "City" || ', ' || COALESCE("Region", 'no region') AS "Label"
FROM "Customers"
ORDER BY "CustomerID"
LIMIT 3;

COALESCE hands back the first of its arguments that is not NULL. Give it as many as you like — COALESCE("Mobile", "HomePhone", 'no number') works through them in order.

Wrap the piece that can be missing, not the whole line. COALESCE("City" || ', ' || "Region", "City") would also produce output, but it throws the city away and re-adds it, which is harder to read and easier to get wrong. Fix the hole, not the wall around it.

And be clear about what COALESCE does: it changes what this query returns. It does not change the data, and it does not make the value known.

NULLIF: take something away

NULLIF is COALESCE backwards. It returns NULL when its two arguments are equal, and the first argument otherwise:

SELECT "ProductName", NULLIF("UnitsInStock", 0) AS "InStock"
FROM "Products"
ORDER BY "UnitsInStock", "ProductName"
LIMIT 3;

Two reasons you would want that.

The first is when a placeholder in your data really means "not recorded" — an imported 0, an empty string, a 'N/A' — and you want it to behave like the missing value it actually is.

The second is guarding a division. x / NULLIF(y, 0) gives you NULL when y is zero instead of failing, because dividing by nothing gives nothing.

But notice what NULLIF is really doing: a zero and a blank are not the same claim. "None left" is something a person counted; "nobody knows" is not. Turning one into the other is a decision about what your data means, which is exactly why it has to be asked for by name rather than happening on its own.

The numbers you already saw, and now the reason

You met these two counts in the COUNT, SUM, AVG, MIN, MAX lesson and were told they were both correct:

SELECT count(*) AS "Orders",
       count("ShippedDate") AS "Shipped"
FROM "Orders";

count(*) counts rows. count("ShippedDate") counts values in that column — and an empty cell is not a value, so it is not counted. The 27 they differ by are the same 27 as everywhere else on this page.

That is the point worth carrying away: this was never a separate rule about COUNT. It is the rule you met in the very first query, met again somewhere else.

And the one that reaches a slide

Every aggregate skips missing values. On SUM, MIN and MAX that is usually what you wanted. On AVG it moves the denominator, and that is where it stops being harmless.

How long do we take to ship an order?

SELECT round(avg(
         CAST("ShippedDate" AS DATE)
       - CAST("OrderDate" AS DATE)
       ), 1) AS "AvgDaysToShip"
FROM "Orders";

304 days of shipping, spread over 52 orders — not 79. The 27 that never shipped are not in that number as zero, and not as late. They are not in it at all.

And they are the slow ones. The average says the company ships in under six days, and it got there by leaving out every order it is late on. That is not a rounding error; it is a claim that is false, wearing a decimal point.

The habit that follows is cheap: when an average matters, print the count beside it. count(*) and count(<the column>) in the same query answer the reader's first question before they ask it.

And now you can fix the blank tile

The COUNT, SUM, AVG, MIN, MAX lesson left you with one more empty answer and no cure for it. We have never shipped an order to Japan, so WHERE throws away all 79 rows — and the query still comes back with a row:

SELECT count(*) AS "Orders",
       sum("Freight") AS "Freight",
       COALESCE(sum("Freight"), 0) AS "Freight, filled"
FROM "Orders"
WHERE "ShipCountry" = 'Japan';

count says 0, because zero of something is a real number. sum has nothing to add up, so there is no total and SQL will not invent one — that is the blank in the middle column, and it is what leaves a dashboard tile empty where a 0 belonged.

Now you know the word for it. COALESCE(sum(...), 0) says "if there was nothing to add up, show a zero" — and it is your decision to say so, made in the open, rather than a blank arriving by accident.

Where you put it decides what it means, and this is the same rule as the label above, one level up:

sum(COALESCE("Freight", 0))   -- fills the HOLES IN THE COLUMN. Pointless:
                              -- sum already skips empty cells, so the answer
                              -- is identical either way.
COALESCE(sum("Freight"), 0)   -- fills the EMPTY ANSWER. This is the one you
                              -- wanted: it only ever does anything when there
                              -- were no rows at all.

Wrap the hole, not the wall around it. Here the hole is the result, not the column.

And keep the honesty check from the COUNT, SUM, AVG, MIN, MAX lesson beside it: a 0 you wrote with COALESCE and a 0 the data really contained look identical on a slide. If the difference matters — and for "revenue in Japan" it does — the count(*) in that same query is what tells the two apart.

The one place NULLs are treated as equal

Everything above says a NULL is not equal to another NULL. There is one family of exceptions, and it surprises people because it looks like a contradiction. It is not — it is a different operation, one that groups rather than compares.

SELECT DISTINCT "Region"
FROM "Customers"
ORDER BY "Region";

Five rows: four real regions, and one row standing for all twenty-one empties. DISTINCT did not keep twenty-one of them. CloudBeaver prints that last row as [NULL] — the same marker you will see in the GROUP BY below, and the same one it uses everywhere a cell is empty rather than blank.

SELECT "Region", count(*) AS "Customers"
FROM "Customers"
GROUP BY "Region"
ORDER BY "Customers" DESC, "Region";

GROUP BY gathers every empty region into a single group of 21. And that group total is worth noticing on its own: unlike every WHERE on this page, nothing was lost — the five groups add up to all 25 customers.

The rule underneath both is that DISTINCT, GROUP BY, UNION and ORDER BY treat two NULLs as not distinct from each other, while = treats them as not known to be equal. Two different questions, two different answers, both correct.

Then one more twist in the same paragraph, so you are not caught by it:

SELECT count(DISTINCT "Region")
FROM "Customers";   -- 4, not 5

count(<column>) never counts empties, DISTINCT or not. So DISTINCT "Region" returns five rows and count(DISTINCT "Region") says four. Both are right; they are answering "which values appear" and "how many values appear".

The one place nothing is lost — and it still catches people

Every bug on this page has removed rows. Sorting does not, which is exactly why it is missed.

SELECT "OrderID", "ShippedDate"
FROM "Orders"
ORDER BY "ShippedDate" DESC NULLS LAST;

All 79 orders come back. Nothing is dropped, nothing is skipped, the 27 blanks are still in the result. They are simply somewhere in the list — and that word is the catch.

Leave the NULLS LAST off, run plain ORDER BY "ShippedDate" DESC, and the two engines this course puts you on disagree:

Both are correct. The standard leaves it implementation-defined, so neither engine has a bug — and because this course has you running koans on DuckDB and CloudBeaver on PostgreSQL, it is not a theoretical portability worry. You can see it today. Ascending, the two agree: blanks go last on both, which is worse in its way, because it means the problem only appears the day somebody adds DESC.

So say it yourself. NULLS LAST (or NULLS FIRST) is standard, portable, two extra words, and it makes the sort the same everywhere. The query above pinned the blanks to the bottom; say the other half of the pair and they go on top instead — on both engines, which is the whole point:

ORDER BY "ShippedDate" DESC NULLS FIRST

Things you will read but rarely write

IS NOT DISTINCT FROM. The comparison that treats two NULLs as equal: a IS NOT DISTINCT FROM b is TRUE when both are NULL, where a = b would be UNKNOWN. Portable across PostgreSQL and DuckDB, genuinely useful when comparing two nullable columns, and rare enough that you should write a comment when you use it.

IS TRUE / IS FALSE / IS UNKNOWN. These test the result of a condition rather than a value, and they always answer. WHERE ("ShippedDate" >= DATE '2024-01-01') IS NOT TRUE catches the 27 as well as the 34 — a compact alternative to the OR ... IS NULL above. Correct, portable, and harder for the next reader, which is why the OR version is the one taught here.

NVL, IFNULL, ISNULL. Vendor-specific versions of COALESCE from Oracle, MySQL and SQL Server. Recognise them; write COALESCE, which is standard and works everywhere.

Where NULL is not this lesson's problem

One boundary worth naming, because it uses the same word for a different question.

Whether a column should allow NULL at all is a design decision, and it belongs to the Data Modeling course. This lesson is about querying data that is already missing. That one is about whether the missing value should ever have been permitted — a NOT NULL constraint, a default, a separate table. Same word, different job, and the answer to a query problem is almost never "change the schema".

What you can do now

For any column that is allowed to be empty — and most of them are:

There are four places to look, and they are the four clauses a column can appear in:

1. Comparing it — IS NULL or IS NOT NULL, never = or <>, and never = '' either. All three of those run and match nothing.
2. Filtering on it — say out loud what should happen to the empty rows, usually OR <column> IS NULL. A condition and its opposite do not cover the table, and wrapping one in NOT does not fix it.
3. Joining or sorting on it — a join's ON drops them exactly as a WHERE does; a sort keeps every row but moves them, and where it moves them is not portable unless you write NULLS LAST.
4. Counting or averaging it — check the denominator. count(<column>) and AVG both quietly work on a smaller set than you think, so print the row count beside the number.

And whatever the clause: wrap the piece that can be missing with COALESCE before you concatenate it or do arithmetic with it, or the whole expression comes back empty.

And one question to ask out loud whenever somebody hands you a filter: what should happen to the rows where this column is empty? SQL will not choose for you — it will just leave them out and say nothing.

Next lesson: SELECT + JOIN + GROUP BY — the last of Series 1 · SQL Fundamentals, and the one where every clause you have met turns up in the same query.

The koans: now you write the queries

Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _45

On macOS or Linux, run ./zeus.sh koans learnsql series1 _45 from the same folder. The three arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_45\NullAndThreeValuedLogicKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.

The fifteen of them deliberately use different tables from everything above — mostly the supplier list, the staff list and the shelf, with one that comes back to the order book to watch what an average divided by. You cannot answer them by copying a query off this page, which is the point. They are grouped by the same four places this lesson ends on:

• Comparing it — test for an empty cell, predict what = NULL really returns, find the rows that do have a value, and predict what = '' returns, which is nothing for a completely different reason.
• Filtering on it — meet the not-equals trap head on, predict how little NOT helps, then ask a column where it is empty before you write a filter against it — and find that the gaps are not spread evenly at all.
• Joining or sorting on it — watch a join's ON drop the one employee whose manager column is empty, then pin the empties to the bottom of a sort with NULLS LAST so the answer means the same thing on both engines.
• Printing and counting it — stand a word in with COALESCE, watch one missing piece empty a whole line, count rows against values, see which denominator an average actually divides by, and turn a value into a NULL on purpose with NULLIF.

The last one is the real exam: a whole query from scratch, needing both the filter fix and the output fix at once.

What is next

That is the last of the traps. The next lesson closes Series 1 · SQL Fundamentals, and it is the one everything has been building to: a single question from your manager, answered with one query across three tables that joins, groups and adds up all at once — SELECT + JOIN + GROUP BY, and every habit you have picked up on the way.