Advanced

The blank that is not zero

I had a column where some rows had no value, and my filter for the missing ones returned nothing. A blank is its own thing, and it does not compare equal to anything.

What you will see

city Rome Paris none sqlite>

About 2 min of reading

Try It

Create a column with a missing value and see how the blank behaves.

Insert a row where the city column is left out, so it holds no value.

INSERT INTO cities (id) VALUES (3);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Find the blank with IS NULL. The row with no city is the one that matches.

SELECT id FROM cities WHERE city IS NULL;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 3 sqlite>

The intuitive test finds nothing, because = NULL never matches. That empty result is the lesson.

SELECT id FROM cities WHERE city = NULL;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id sqlite>

Replace the blank in the answer with a chosen word using COALESCE.

SELECT COALESCE(city, "none") FROM cities;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

city Rome Paris none sqlite>

Data Flow

A missing value is NULL, comparisons against it are unknown, and IS NULL or COALESCE are the tools that handle it.

1 A cell holds no value

↓

2 That cell reads as NULL

↓

3 = NULL never matches

↓

4 IS NULL or COALESCE handle it

Why this works

NULL is its own value

NULL means the value is absent, and it is not zero, not an empty string, not false. Any comparison against NULL is itself unknown, so = NULL never matches and <> NULL never matches either. That is why a filter for missing data returns nothing when you write it the intuitive way.

NULL spreads through math

Any arithmetic on NULL gives NULL. NULL + 1 is NULL, not 1. So a SUM over a column with blanks ignores the blanks, but a per-row expression touching a blank turns the whole result blank. When you need a stand-in, COALESCE turns a blank into a value you choose before the math runs.

Examples

A blank is not empty text

SELECT city FROM cities WHERE city = "";

This finds nothing, because a missing value is not an empty string. The blank is NULL, a special marker that means no value at all.

Ask with IS

SELECT name FROM users WHERE city IS NULL;

To test for a blank you write IS NULL, not = NULL. The word IS is a different test, built for the blank. IS NOT NULL is the opposite.

Fill a blank in

SELECT COALESCE(city, "none") FROM users;

COALESCE returns the first argument that is not blank. When city is missing you get none in the answer instead of a blank.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Computing inside the query

Previous Lesson

Computing inside the query

Next Lesson

Making lookups fast

Making lookups fast →