NULL: the empty plate · Sushi & Syntax

Remember the end of lesson 04? The LEFT JOIN kept every order, and temaki came back with a price of null. That little word trips up more people than any other in SQL. It looks like a value. It refuses to behave like one. So let us put it on the counter and look at it properly: null is an empty plate, and the kitchen has strict rules about empty plates.

Zero, empty text, and nothing at all

Three plates that beginners constantly mix up:

0 is a real value. Someone counted and the answer was zero.
'' is a real value too: a piece of text that happens to contain no letters. The plate is not empty, there is an invisible slip of paper on it.
null is no value at all. Nobody wrote anything down. Not zero, not empty text. Unknown.

fig 1 · zero and empty text are values, null is the absence of one

Watch the two chips take turns at the empty plate. The = NULL question bounces straight off. Only IS NULL gets the plate to light up. The next two sections explain why.

Where empty plates come from

You rarely type null on purpose. It sneaks in:

Optional columns. A customers table with a phone column: some guests simply never gave one. Their rows still exist, the phone cell is null.
LEFT JOINs. As in lesson 04: temaki had no menu match, so its price came back null. The database kept the row and shrugged about the missing columns.
Explicit inserts. INSERT INTO menu VALUES ('uni', NULL) says: this item exists, the price is not decided yet.

In every case the meaning is the same: the row is real, this particular fact about it is unknown.

Why `= NULL` never matches

Here is the trap. You want the rows with a missing price, so you write:

SELECT * FROM menu WHERE price = NULL;

Zero rows. Always. Even for rows where the price really is null.

The reason is honest, once you see it. null means unknown. So price = NULL asks: is this value equal to something unknown? The only truthful answer is: unknown. And the WHERE gate from lesson 02 only passes rows that answer yes. Unknown is not yes. Every row gets turned away.

SQL gives you a separate question that is actually about emptiness:

SELECT * FROM menu WHERE price IS NULL;
SELECT * FROM menu WHERE price IS NOT NULL;

IS NULL does not try to compare anything. It just lifts the plate and checks: is there something on it, or not? That question always has a clear yes or no answer.

COALESCE brings the house default

Sometimes you do not want to filter the empty plates, you want to fill them. COALESCE takes a list of values and returns the first one that is not null:

SELECT item, COALESCE(price, 0) AS price FROM menu;

Now temaki shows 0 instead of null. You can chain fallbacks too: COALESCE(nickname, name, 'guest') tries each plate in order and serves the first one that has something on it.

One warning before you sprinkle it everywhere: COALESCE(price, 0) turns “price unknown” into “price is zero”. Fine for a display, dangerous in a calculation. Only swap in a default when the default is honestly what you mean.

Try this yourself

Open your playground and start with a guaranteed win:

SELECT NULL IS NULL;

That returns true (shown as 1 in most tools). You just asked the only question null will ever answer clearly.

Now build a tiny menu with a hole in it:

CREATE TABLE menu (item TEXT, price REAL);
INSERT INTO menu VALUES
  ('maki', 4), ('temaki', NULL), ('gunkan', 6);

SELECT * FROM menu WHERE price = NULL;
SELECT * FROM menu WHERE price IS NULL;
SELECT item, COALESCE(price, 0) AS price FROM menu;

Before running each SELECT, predict what comes back. The first one is the trap from this lesson: watch it return nothing at all. Then try SELECT NULL = NULL; and compare it with your very first query. Same plates, different question, completely different answer.