IS NULL¶

Goal: Find missing values correctly. Understand why field = NULL never works.

NULL is "unknown"¶

In SQL, NULL means "we don't know" — not zero, not empty string, not false.

These are all different.

Why = NULL doesn't work¶

notes = NULL          -- ❌ never true
notes <> NULL         -- ❌ never true

Comparing anything to NULL with = or <> returns "unknown" — which WHERE treats as false.

The right way¶

notes IS NULL          -- ✅ rows with no notes
notes IS NOT NULL      -- ✅ rows with any non-null value

Common patterns¶

Missing data¶

EMAIL IS NULL                 -- no email captured
PHONE IS NULL OR PHONE = ''   -- no phone (NULL OR empty)
SURVEY_DATE IS NULL           -- never surveyed

Find records that have data¶

PHOTO_URL IS NOT NULL
NOTES IS NOT NULL AND NOTES <> ''

Combine with other conditions¶

STATE = 'GA' AND POP IS NOT NULL
LANDUSE LIKE 'Res%' AND OWNER IS NULL

NULL vs empty string¶

These are not the same:

If your data was imported from Excel or CSV, you might have a mix. Defensive query:

PHONE IS NULL OR TRIM(PHONE) = ''

(TRIM is supported in most enterprise databases; in file gdb you can use Python Calculate Field to clean first.)

NULLs and arithmetic¶

Any arithmetic with NULL returns NULL:

PRICE - DISCOUNT             -- NULL if either is NULL
ENROLLMENT * 1.1             -- NULL if ENROLLMENT is NULL

To handle, use COALESCE (SQL Server, PostgreSQL):

COALESCE(DISCOUNT, 0)        -- treat NULL as 0

In file geodatabases, this isn't always supported — calculate a clean field first.

NULLs with NOT¶

NOT (STATE = 'GA')

Excludes rows where STATE IS NULL — because STATE = 'GA' is unknown for nulls. To include them:

NOT (STATE = 'GA') OR STATE IS NULL

This is the "NULL trap" that bites everyone.

NULLs with IN¶

STATE NOT IN ('GA','FL','SC')

Same trap — excludes NULL states. Add OR STATE IS NULL if you need them.

NULLs in numeric fields¶

POP > 100000

Excludes NULL POP rows (technically "unknown > 100000"). Usually that's fine. But for complement analysis, be explicit:

POP <= 100000 OR POP IS NULL    -- everything not over 100K

Quick checklist¶

When writing any query:

1. Are there fields that might be NULL?
2. Do I want NULLs included or excluded?
3. If excluded, am I using IS NOT NULL?
4. If included, do I have OR field IS NULL?

Practice¶

→ Next: Numeric Filters.