This is the lesson where the debt gets paid. Since 01-03 you've been tripping over the same phenomenon in different disguises: employee_id = NULL returned zero rows when there were ten; employee_id <> 4 returned six instead of sixteen; the ten web orders vanished from an INNER JOIN; a condition on the right table in the WHERE degraded a LEFT JOIN into an INNER; and only yesterday, NOT IN (4, 5, NULL) returned exactly nothing. Every time we promised you it "gets explained in 04-03".

Here it is. All those cases are the same thing, and that thing is called three-valued logic. By the end of this lesson you won't have learned five new rules: you'll have learned one, and all those symptoms will stop surprising you because you'll be able to predict them. You'll also see the part almost nobody explains: that the SQL standard treats NULLs one way in the WHERE and a completely different way in DISTINCT, GROUP BY and ORDER BY, and that knowing that inconsistency is what separates those who suffer nulls from those who use them.

1. What NULL is and what it isn't
2. GreenStore's three NULLs and what they mean
3. Three-valued logic: TRUE, FALSE and UNKNOWN
4. The complete truth tables
5. IS NULL and IS NOT NULL
6. IS DISTINCT FROM and IS NOT DISTINCT FROM
7. Where NULL does group with NULL
8. NULL and the UNIQUE constraint
9. NULL in concatenations and in arithmetic
10. Resolving the outstanding cases
11. Designing with NULL, with sentinels or with NOT NULL
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

NULL is the absence of a value. It isn't a special value: it's the marker saying there isn't one in that cell.

The most widespread confusion consists of treating it as if it were a concrete value. It isn't, and the difference shows as soon as you compare:

That last point is the heart of the whole thing. If there are two rows in products.cost with NULL, are their costs equal? You don't know. They could be €3.20 and €47. SQL, quite rightly, refuses to state that they're equal; and it also refuses to state that they're different.

The sentence that sums the lesson up: NULL doesn't mean "empty", it means "unknown". And anything you compare with something unknown produces an unknown result.

In a well-designed schema, every column that allows nulls does so for a specific business reason. GreenStore has three, and all three mean different things:

Let's look at them.

10 rows, 50 % of the sales channel.

7 rows. The other 8 customers arrived on a recommendation.

1 row. This NULL isn't an absent piece of data or an unknown one: it's a structural statement ("the hierarchy ends here"), and that's why 03-06's SELF JOIN needed a LEFT JOIN so as not to lose Rosa.

And there's still a fourth kind of NULL you've already seen and which isn't in any table: the ones the engine manufactures when building a LEFT JOIN (03-03, section 3). Those weren't in the data; they appear because there was no partner.

Outside SQL, a condition is either true or false. In SQL there are three possible outcomes, because a comparison with NULL can't be decided:

UNKNOWN isn't some exotic third state: it's exactly what the word says. "Is order 1's employee number 4?" — we don't know, because there's no employee on record.

And now the rule that governs everything, already stated in 02-03 and worth repeating verbatim:

WHERE keeps only the rows whose condition evaluates to TRUE. The ones that give FALSE are discarded, and the ones that give UNKNOWN are too.

The same holds for a JOIN's ON and for the HAVING you'll see in 04-06. One criterion, three clauses.

There's the key to why false and unknown look the same when you're looking at a single query's result: both discard the row. The difference only becomes visible when you negate the condition, because NOT FALSE is TRUE but NOT UNKNOWN is still UNKNOWN.

The two highlighted cells are the important ones: FALSE AND UNKNOWN is FALSE, not UNKNOWN. If one of the factors is already false, the whole thing is false even if the other is unknown. It doesn't matter what you don't know: the conjunction is already decided.

Symmetrically: TRUE OR UNKNOWN is TRUE. If one alternative is already true, the disjunction is true.

These two cells on their own explain 04-02's asymmetry:

Negating the unknown is still unknown. From this comes the most useful practical observation in the whole lesson:

A condition and its negation don't cover every row. If status = 'delivered' gives 14 rows and status <> 'delivered' gives 6, and the table has 20, the logic closes because status is NOT NULL. With employee_id = 4 (4 rows) and employee_id <> 4 (6 rows), 4 + 6 = 10 ≠ 20: the 10 missing ones are the nulls. That sum is your best null detector.

The truth tables describe the meaning, not the order in which PostgreSQL evaluates the conditions. The engine may freely reorder an AND's operands if that makes the plan cheaper. That's why you can't use an AND as protection:

PostgreSQL could evaluate the division first. The safe form is CASE or NULLIF, both from module 6.

Since = NULL is never TRUE, SQL offers a specific predicate. IS NULL isn't a comparison: it's a question about the cell's state, and that's why it always returns TRUE or FALSE, never UNKNOWN.

The first column is NULL (that is, UNKNOWN); the other two are genuine booleans. That's the whole difference, and it explains why WHERE employee_id IS NULL works and WHERE employee_id = NULL doesn't.

And the IS NULL / IS NOT NULL pair does split the table into two exact halves:

10 + 10 = 20. It closes. (FILTER and COUNT are the next lesson's subject; here they're only acting as a measuring instrument.)

Dialect note: some engines let you configure = NULL to behave like IS NULL —SQL Server does it with SET ANSI_NULLS OFF, now deprecated. Never turn it on. It turns your SQL into something that only works on your server and breaks the logic you've just learned.

Sometimes you do want to compare treating NULL as "just another value": having two nulls count as equal and a null count as different from any value. That's what this pair of operators exists for, and they never return UNKNOWN.

With real data, the difference jumps out. "All the orders Óscar Peris (employee 4) didn't handle":

6 rows. It's the result that surprised you in 02-03.

16 rows: the previous 6 plus the 10 web orders. Now, at last, 4 + 16 = 20.

The equivalent long form would be WHERE employee_id <> 4 OR employee_id IS NULL, which is what you wrote by hand in solution 3 of 02-03. IS DISTINCT FROM says the same thing in four words with no risk of forgetting the second term.

Its most valuable use, though, appears when comparing two columns that can both be null:

With <>, a row whose cost goes from NULL to 12.00 wouldn't be detected as a change (the comparison would give UNKNOWN). With IS DISTINCT FROM, it would. It's the reference operator for change detection, version comparison and synchronisation processes.

Dialect note: IS DISTINCT FROM is standard SQL and exists in PostgreSQL, SQLite and Oracle 23ai. MySQL uses the <=> operator ("null-safe equal"), which is equivalent to IS NOT DISTINCT FROM. SQL Server had nothing until 2022, when it introduced IS [NOT] DISTINCT FROM; in earlier versions you have to write the long form with OR ... IS NULL.

Here comes the part that throws a lot of people: the SQL standard isn't consistent with itself. You've just learned that NULL = NULL is UNKNOWN, and yet:

4 rows. Ten orders have a null employee_id and DISTINCT has collapsed them into a single row. In other words: as far as DISTINCT is concerned, those ten nulls are equal to each other.

The same happens when grouping:

4 groups, and the NULLs form one of their own with 10 rows. This is enormously useful —in fact it's what lets you count the web channel at a glance— and lesson 04-05 will develop it.

And when sorting, the nulls also cluster together, though their position is configurable (02-05):

(First 12 of 20 rows.)

The standard's official justification is that WHERE answers questions about facts (and you can't state anything about an unknown), whereas GROUP BY and DISTINCT answer questions about grouping rows (and there "no value" is as valid a category as any other). It's a reasonable explanation, but it doesn't change the fact that the same symbol behaves in two ways. Learn the table; don't try to derive it.

A direct consequence of the penultimate row of that table: in PostgreSQL, a column with a UNIQUE constraint allows as many NULLs as you like, because two nulls aren't considered duplicates.

This is surprising, and sometimes it's exactly what you want ("the EAN is unique, but not every product has one yet") and sometimes it isn't ("there can only be one unverified customer"). PostgreSQL 15 added a way to demand the opposite:

In GreenStore the two UNIQUE columns —categories.name and customers.email— are also NOT NULL, so the question never comes up. But it's a design detail to decide consciously, not to discover in production.

Dialect note: this is one of the points where engines diverge the most. PostgreSQL, MySQL, SQLite and Oracle allow several nulls in a UNIQUE column; SQL Server allows only one (it treats every null as the same value for the purposes of the unique index). A table that works in PostgreSQL can fail when migrated to SQL Server for this exact reason. Constraints are studied in module 5.

And remember that a primary key doesn't have this debate: PRIMARY KEY implies NOT NULL, always and on every engine.

The rule is brutally simple: almost any operation involving NULL returns NULL. The null is said to propagate.

You saw it in 02-02 with concatenation, and in 03-03 with LEFT JOINs. Here's the reason: if you don't know what the second addend is, you can't know what the sum is.

Over real data, the effect in a LEFT JOIN:

double_shipping is NULL, not 0.00, for the three customers with no orders. In a report that can show up as an empty cell, and whoever reads it may take it for a zero. It isn't: it's "not applicable".

Not everything propagates the null. It's worth knowing the exceptions because they're precisely the tools for handling it:

You already handle the first three rows. The COALESCE, NULLIF and CASE ones —the ones that replace a null with something presentable— are the content of 06-04 and 06-05; there you'll write COALESCE(o.shipping_cost, 0) so the report's cell shows 0.00. And the aggregate one is the first thing you'll see tomorrow.

Let's close, one by one, all the course's loose ends. All five have the same explanation.

employee_id = NULL evaluates to UNKNOWN for all twenty rows: for the ten with a value because you can't compare a number with the unknown, and for the ten null ones because NULL = NULL isn't true either. The WHERE discards everything that isn't TRUE. Zero rows, and it couldn't be otherwise. The correct form is IS NULL.

The ten web orders evaluate NULL <> 4 → UNKNOWN → discarded. That leaves the 10 with a sales rep, minus Óscar's 4: 6 rows. If the question was "the ones Óscar didn't handle", the correct answer has 16 rows and is written WHERE employee_id IS DISTINCT FROM 4, as in section 6.

The condition ON o.employee_id = e.id is a comparison like any other. For the web orders it gives UNKNOWN, no row of employees satisfies it, and the INNER JOIN discards them. The ON follows the same rule as the WHERE.

The LEFT JOIN manufactures a row for Núria with all of orders' columns at NULL. Then, WHERE o.status = 'delivered' evaluates NULL = 'delivered' → UNKNOWN → discarded. That's why 18 rows became 14 and 15 customers became 12. The condition has to go in the ON, which acts before those nulls are manufactured.

It expands to ... AND employee_id <> NULL, and that factor is UNKNOWN for every row. TRUE AND UNKNOWN = UNKNOWN (section 4's truth table). Zero rows guaranteed, for any table and any data.

The five cases are the same case. Once you see them that way, they stop being five rules to memorise and become one rule to apply.

When it's your turn to design a table (module 5) you'll have to decide, column by column, whether it allows nulls. There are three strategies and none of them is universally right.

Criteria for choosing:

1. Is there a default value that's actually true? If so, NOT NULL DEFAULT. Free shipping really is €0.00, not an absence.
2. Does the absence mean something to the business? If so, NULL, and document it. "Web order" is valuable information, not a gap.
3. Are you tempted to use -1, 0, 'N/A' or '9999-12-31'? Careful: that's a sentinel in disguise. AVG will average it in, MIN will return it as the minimum and COUNT will count it. GreenStore would be a disaster if the web channel had been coded as employee_id = 0: any "average orders per sales rep" would be poisoned.

The sensible stance: NULLs are part of the relational model and hiding them behind sentinels doesn't remove the problem, it just makes it invisible. Use NOT NULL whenever you can justify a real default value; use NULL when the absence is legitimate; and don't use sentinels unless you have a very specific reason and you write it into the schema's documentation.

• Writing = NULL or <> NULL. Zero rows, always. It's IS NULL / IS NOT NULL.
• Believing NULL is zero or the empty string. NULL + 5 is NULL; 0 + 5 is 5. NULL || 'a' is NULL; '' || 'a' is 'a'.
• Forgetting that <>, NOT IN, NOT LIKE and NOT BETWEEN discard null rows. Always check that the condition and its negation add up to the total.
• Using NOT IN with a list that may bring back a NULL. Zero rows, guaranteed (04-02).
• Putting a condition on the right table in a LEFT JOIN's WHERE. The manufactured nulls don't pass it and the LEFT degrades into an INNER (03-03).
• Assuming that if A is NULL then NOT A is true. NOT UNKNOWN is UNKNOWN.
• Reading an empty cell in a report as a zero. It may be "not applicable". COALESCE (06-04) makes it explicit.
• Expecting UNIQUE to prevent several null rows. It doesn't, except with NULLS NOT DISTINCT in PostgreSQL 15+, or in SQL Server, which works the other way round.
• Relying on an AND's evaluation order to protect against a null or a division by zero. The planner reorders. Use CASE or NULLIF (module 6).
• Tip: when reading a schema, look first at which columns allow NULL. \d table in psql tells you. They're exactly the places where your queries can lose rows.
• Tip: when a query returns fewer rows than expected, suspect nulls before the data. It's the most frequent cause and the quietest.
• Tip: use IS DISTINCT FROM by default when comparing two nullable columns. It saves you the OR ... IS NULL and makes the intent explicit.

Without running anything, predict the result (true, false or *(null)*) of each expression. Then run them all in a single query and check.

Explain in one sentence each result that surprised you.

The marketing department wants to measure the referral programme. Over customers:

1. How many customers arrived on a recommendation and how many on their own? Write two queries and check that they add up to 15.
2. Write the query that returns, for each customer, their full name and the full name of whoever recommended them, including those who weren't recommended by anybody. (Hint: a reflexive relationship, 03-06.)
3. In that query, why does the referrer's column come out as *(null)* and not as an empty string? Give both reasons.

A colleague has written this quality check and claims that "there are no odd orders in GreenStore":

1. What is that query really measuring, and why doesn't the 0 prove what he thinks it does?
2. Write the version that genuinely counts the orders whose employee_id is neither 4, nor 5, nor 6 (counting nulls as "it isn't any of the three"). How many are there?
3. Write the version that counts the orders with an employee_id that is present but different from those three. How many are there and what does that number mean?

Solution 1

The three that usually cause surprise are d, f and g. d because you expect negating something to make it true; f and g because it looks contradictory that AND with an UNKNOWN sometimes gives UNKNOWN and sometimes FALSE — and yet it's the logical outcome: if one factor is already false, you don't need to know the other.

Solution 2

1. The two halves:

8 + 7 = 15. It closes, because IS NULL and IS NOT NULL really do partition the table. If you'd written WHERE referred_by_id <> 0 for "the referred ones", you'd have got 8 anyway by pure chance, and WHERE referred_by_id = 0 would have given 0 instead of 7.

2. A self join with a LEFT JOIN:

15 rows, all 15 customers. With an INNER JOIN there'd be 8 and you'd lose the walk-ins. Notice the detail: Lucía has recommended three customers (2, 3 and 15), which makes her GreenStore's best advocate.

3. The two reasons why referred_by comes out as *(null)*:

1. The LEFT JOIN found no partner. For the seven customers with a null referred_by_id, the condition c.referred_by_id = referrer.id gives UNKNOWN and matches no row, so the engine fills all of referrer's columns with NULL (03-03, section 3).
2. Concatenation propagates the null. Even if only one of the two columns were null, referrer.name || ' ' || referrer.last_name would give NULL all the same, because || with a null operand returns null (02-02 and section 9 of this lesson).

That it's *(null)* and not '' matters: the empty string would mean "that's their name, with zero characters". The null means "there's nobody there". For the report to show something readable —"No referrer", say— you need COALESCE, in 06-04.

Solution 3

1. What it's really measuring. The three factors are joined by AND, and for the ten web orders each one is UNKNOWN. UNKNOWN AND UNKNOWN AND UNKNOWN is UNKNOWN, so those ten rows are discarded. For the other ten, each order has an employee_id that is 4, 5 or 6, so one of the three factors is FALSE and the conjunction is FALSE.

Result: zero rows. And that 0 doesn't prove what he thinks, because the query is incapable of seeing half the table: the ten web orders never even get evaluated as candidates. If the criterion for "odd order" includes "no sales rep assigned", this query would never detect one, today or ever. A quality check that structurally can't come out positive isn't a quality check.

2. Counting nulls as "it isn't any of the three":

10 orders: exactly the ten from the web channel. IS DISTINCT FROM never returns UNKNOWN, so the conjunction resolves cleanly. The equivalent and more idiomatic form for this particular case would be:

which returns the same 10.

3. Only the ones that have a sales rep and it isn't any of those three:

0 orders, and this zero does mean something: there's no order assigned to an employee other than Óscar, Laia or Marc. That is, the other five employees —Rosa, Andrés, Beatriz, Irene and Daniel— don't handle orders, exactly as 01-06 described.

The final check: 10 (with sales rep 4, 5 or 6) + 10 (with no sales rep) + 0 (with another sales rep) = 20. Now the logic closes, and that's the criterion for knowing the query is correctly written.

The debt is settled, and with a single idea:

• NULL is the absence of a value, not zero, not the empty string, not false, not equal to another NULL. It means unknown.
• In GreenStore there are three NULLs with three different business meanings —the web channel, the walk-in customer, the top of the org chart— plus the ones the engine manufactures in every LEFT JOIN.
• SQL uses three-valued logic: TRUE, FALSE and UNKNOWN. WHERE, ON and HAVING keep only what's TRUE, so unknown is discarded just like false — but under negation they behave differently, because NOT UNKNOWN is still UNKNOWN.
• The two cells to memorise are TRUE OR UNKNOWN = TRUE and TRUE AND UNKNOWN = UNKNOWN. All the asymmetry between IN and NOT IN comes from them.
• IS NULL / IS NOT NULL are predicates, not comparisons, and they do split the table into two exact halves. IS DISTINCT FROM compares treating the null as just another value, and it's the right tool for comparing nullable columns.
• The standard is deliberately inconsistent: NULL = NULL is unknown, but in DISTINCT, GROUP BY, ORDER BY and the set operations the nulls are considered equal to each other. A UNIQUE constraint, by contrast, considers them different and allows several.
• The null propagates through arithmetic and concatenation: price * NULL is NULL, not zero. COALESCE, NULLIF and CASE (06-04 and 06-05) are the tools for replacing it at presentation time.
• The course's five outstanding cases —= NULL, <> 4, the INNER JOIN that lost orders, the WHERE that degraded the LEFT JOIN and the NOT IN with a null— are the same case: a comparison that gives UNKNOWN and a row that disappears without warning.
• When designing, choose consciously between NULL, a sentinel value and NOT NULL DEFAULT. Sentinels don't remove the problem: they hide it inside the averages and the counts.

That's the end of the module's first half. You now know how to filter precisely: by pattern, by list, by range and by absence of value. In the next lesson, aggregate functions, the second half begins and the nature of what you do changes: until now every row of the result came from a row of the table; from now on many rows will go in and a single value will come out. You'll see COUNT, SUM, AVG, MIN and MAX, and the first thing you'll learn about them is that they all ignore NULLs —all but COUNT(*)— which makes orders.employee_id the best possible test bench: 20, 10 and 3 depending on how you count. And we'll finally resolve the warning repeated three times in module 3 about summing shipping costs after joining with the detail.