NULL Handling

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

Correct null handling is essential for reliable filters.

Core Concepts

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