Lesson 2.8

SQL Three-Valued Logic

A comparison with NULL is neither true nor false but unknown. WHERE drops unknown rows, and NOT can't bring them back; IS TRUE and IS NOT TRUE can.

Lesson 2.6 · Missing values with NULL found the orders with no ship date. Those NULLs also change every comparison they touch. A comparison in SQL has three possible answers, not two: true, false, and unknown, which SQL writes as NULL.

One comparison, three answers

select order_id,
       shipped_date,
       shipped_date < date '2024-04-20' as shipped_before
from orders
where order_date >= date '2024-03-30'
  and order_date < date '2024-04-20'
order by order_date, order_id

Order 14 shipped on April 1, so shipped_before is true. Order 21 shipped on April 20, so it's false. Orders 17 and 4 have no ship date, and nobody can say whether a missing date falls before April 20, so the answer is unknown.

WHERE keeps only true

A WHERE clause keeps a row only when its condition is true. False and unknown are both dropped. That makes a filter and its opposite add up to less than the whole table: of the shop's 24 orders, 10 shipped before April 20 and 7 didn't. The other 7 have no ship date, and appear under neither where shipped_date < date '2024-04-20' nor its NOT.

NOT can't rescue them, because the opposite of unknown is still unknown. AND and OR give a definite answer only when the other side settles it:

Unknown through AND, OR, and NOT

select null::boolean and false as null_and_false,
       null::boolean and true as null_and_true,
       null::boolean or true as null_or_true,
       null::boolean or false as null_or_false,
       not null::boolean as not_null

Unknown AND false is false, because one false side is enough. Unknown OR true is true, because one true side is enough. Every other combination, and NOT unknown, stays unknown. Flag columns follow the same rules: if discontinued from Lesson 2.5 · Booleans and flags allowed NULLs, where not discontinued would skip every product whose flag was missing.

IS TRUE and IS NOT TRUE

Four tests never answer unknown: IS TRUE, IS NOT TRUE, IS FALSE, and IS NOT FALSE. Each asks a yes or no question about the answer, so an unknown gets a definite reply:

Tests that never return unknown

select null::boolean = true as equals_true,
       null::boolean is true as is_true,
       null::boolean is not true as is_not_true,
       null::boolean is false as is_false,
       null::boolean is not false as is_not_false

= true passes the unknown along, while is true answers false. That makes where (condition) is not true the exact opposite of where condition: it keeps every row the original drops, the false ones and the unknown ones. To compare two values that might both be NULL, see Lesson 7.4 · Null-safe filtering, which uses IS DISTINCT FROM.

Syntax reference

Need syntax details? See the SQL NULL handling reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Take a snapshot of the shipping backlog as it stood on the morning of April 20, 2024: every order placed before April 20 that had not shipped before April 20, including orders with no ship date at all. Keep cancelled orders in. Return order_id, order_date, shipped_date, and status, sorted by order date, then order id. The starter's report finds one order.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: order_id, order_date, shipped_date, status.
3 rows: orders 17 and 4, which have no ship date, and order 21, which shipped on April 20.
Order 17 is cancelled. It stays in: the question is about shipping, not status.

← Previous · 2.7 Integers, decimals, and floats

End of Chapter 2

That's the last lesson in “Data Types Before Deep Querying.”

The next chapter builds on what this one covered.

Start Chapter 3: Filtering in Depth → Begins with 3.1 Choosing columns and DISTINCT

Reference

SQL NULL Handling

IS NULL, COALESCE, NULLIF, and null-safe logic.

Mistake

Why = NULL Never Matches in SQL

Use IS NULL instead of equality.

Lesson