NULL Handling

NULL means "missing" or "unknown", not zero and not empty string.

Correct null handling is essential for reliable filters.

Core Concepts

flowchart LR
  A[Column value] --> B{Is NULL?}
  B -->|Yes| C[Use IS NULL branch]
  B -->|No| D[Use normal comparisons]

Check	Correct syntax	Avoid
Is null	column IS NULL	column = NULL
Is not null	column IS NOT NULL	column != NULL

Code Examples

-- Setup table with missing values.
CREATE TABLE profiles_null (
  profile_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  phone      TEXT
);

INSERT INTO profiles_null (name, phone)
VALUES ('Ava', NULL), ('Ben', '123-456'), ('Chen', NULL);

Expected output
Table created and three rows inserted (two with NULL phone).

-- Correct null filter.
SELECT profile_id, name
FROM profiles_null
WHERE phone IS NULL;

profile_id	name
1	Ava
3	Chen

-- Non-null filter.
SELECT profile_id, name, phone
FROM profiles_null
WHERE phone IS NOT NULL;

profile_id	name	phone
2	Ben	123-456

SQLite-Specific Nuances

SQLite Nuance

In SQLite, NULL follows SQL three-valued logic. Comparisons with NULL using = do not evaluate to true.

Common Pitfalls / Best Practices

Pitfall

Writing WHERE phone = NULL and expecting null rows.

Best Practice

Use IS NULL / IS NOT NULL consistently, and model optional data intentionally.

Quick Challenge

Count how many rows in profiles_null have missing phone.

View Solution

SELECT count(*) AS missing_phone_count
FROM profiles_null
WHERE phone IS NULL;

missing_phone_count
2