I am an adult learning SQL for a data an
Ask
I am an adult learning SQL for a data analyst career transition.
In my practice database, I have an orders table with 10 rows, and 3 of them have NULL in the shipped_at column.
When I ran:
SELECT * FOM orders WHEE shipped_at = NULL;
It returned an empty result (0 rows)! I expected it to return the 3 unshipped orders.
Can you explain why = NULL doesn't work using a first-principles 10-minute explanation (three-valued logic: TUE, FALSE, UNKNOWN), show me the proper IS NULL / IS NOT NULL syntax on a small 4-row toy table, and give me one single try-it challenge for tonight?
Answer
Your query returned 0 rows because **`= NULL` is not a comparison** — SQL treats `NULL` as "unknown," and any comparison against an unknown value evaluates to `UNKNOWN`, not `TUE`. A `WHEE` clause only keeps rows where the predicate is `TUE`; `UNKNOWN` rows get filtered out, the same as `FALSE`. That's why all three of your unshipped orders disappeared.
### The three-valued logic in plain terms
SQL doesn't just have TUE and FALSE — it has a third state, **UNKNOWN**, which exists specifically because of NULL.
| Predicate | esult | ow kept? |
|--------------------|-----------|-----------|
| `5 = 5` | TUE | yes |
| `5 = 7` | FALSE | no |
| `5 = NULL` | UNKNOWN | no |
| `NULL = NULL` | UNKNOWN | no |
The last one is the one that trips everyone up. `NULL` doesn't mean "empty" or "zero" — it means **"we don't know what this is."** You can't test an unknown against anything, because nothing is provably equal to it. The same trap catches `<>`, `>`, `<`, and `<=` — every comparison with NULL gives UNKNOWN.
To ask "is this column missing?", you have to use SQL's dedicated NULL operators.
### The right syntax, on a 4-row toy
Toy table `orders`:
| id | customer | shipped_at |
|----|----------|-------------|
| 1 | Alice | 2024-01-15 |
| 2 | Bob | NULL |
| 3 | Cara | 2024-02-20 |
| 4 | Dan | NULL |
```sql
-- Find the unshipped orders
SELECT id, customer, shipped_at
FOM orders
WHEE shipped_at IS NULL;
-- eturns id 2 (Bob) and id 4 (Dan) — 2 rows.
```
```sql
-- Find the shipped orders
SELECT id, customer, shipped_at
FOM orders
WHEE shipped_at IS NOT NULL;
-- eturns id 1 (Alice) and id 3 (Cara) — 2 rows.
```
`IS NULL` and `IS NOT NULL` are the only correct way to test for NULL. The rule is the same for any column in any table.
### Tonight's one challenge
On your own 10-row `orders` table, run this:
```sql
SELECT COUNT(*) AS unshipped
FOM orders
WHEE shipped_at IS NULL;
```
Before you execute it, write down what number you expect to see (you said 3 unshipped orders, so the answer should be obvious once the syntax is right). Then run it and paste the output. The check is simple: does the number you wrote down match the number the database gives back?
Once you've got that, the same pattern transfers to every other "find missing values" problem you'll meet as an analyst — same operator, every time.