Subsection 5.4

NULLs and the gotcha

~4 min

Reading

A field with a NULL value has no value.

Try this analogy

Think of NULL as a blank on the sign-in sheet

You collect sign-in sheets at an event. Some people filled in their phone number. Some left it blank. A blank space is not the same thing as the number zero, or a typed-out word "none," or a hyphen. It's just blank. The information is missing.

That blank space is what databases call NULL. It means "we don't know." And because we don't know, you can't ask "is this column equal to NULL?" : the database can't answer "is unknown equal to unknown?" with anything except "unknown."

That's why WHERE phone = NULL silently returns nothing. You have to ask the right question: WHERE phone IS NULL (the field was left blank) or WHERE phone IS NOT NULL (the field has a value, whatever it is). It's the difference between asking "do they have a phone number on file?" versus "is their phone number equal to mystery?"

NULL is not the same thing as zero. It is not the same thing as an empty string. It is not the same thing as a field containing spaces. It means the value is missing, unknown, or not applicable.

If a field is optional, a row can be inserted or updated without adding a value to that field. The field may then be saved as NULL.

This matters for analysis. If you are counting missing phone numbers, you need to know how missing values appear in the table.

You cannot test for NULL with ordinary comparison operators like =, <, or <>.

Do not write:

WHERE tmc_cell_phone = NULL

Use IS NULL:

SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE tmc_cell_phone IS NULL;

Use IS NOT NULL to find rows where a value exists:

SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE tmc_cell_phone IS NOT NULL;

The logic is important. NULL means unknown. SQL does not treat unknown as equal to unknown. That is why column = NULL does not work the way many beginners expect.

For organizing work, NULL checks are useful for questions like:

Which records are missing phone numbers?
Which contacts have no email?
Which RSVPs have no source?
Which addresses need cleanup before canvassing?

Learner action

Find one nullable field in your table. Write a query that checks whether it IS NULL or IS NOT NULL.

From the deck (slide 39):

"A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation."

Why `= NULL` doesn't work

NULL means "unknown." When you write column = NULL, SQL translates it as "is the value of column equal to an unknown value?" The answer is also unknown : so the row is not returned. Same for column != NULL.

The fix: IS NULL and IS NOT NULL

Examples from the deck (slide 40)

-- Find voters with NO cell phone on file
SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE tmc_cell_phone IS NULL;

-- Find voters who DO have a cell phone on file
SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE tmc_cell_phone IS NOT NULL;

Why this matters for organizing

From Rosario's speaker note: before a phonebank, you need to know how many people in your file actually have phone numbers. IS NULL answers that. Aggregate functions like AVG() silently skip nulls, which can quietly distort your reporting if half the column is blank.

Action: Look at your table. Pick a field that might be blank for some rows. Decide whether your WHERE should include nulls, exclude them, or target them specifically.

Slide to complete