NULL Handling Best Practices
intermediateadvanced
2 min read
What is NULL in SQL?
NULL represents the absence of a value. It is not zero, not an empty string, and not false. NULL is unknown, and any operation involving NULL yields NULL (with few exceptions).
Key Functions
-- Check for NULL
WHERE column IS NULL
WHERE column IS NOT NULL
-- Provide defaults
COALESCE(column, 'default')
-- Conditionally return NULL
NULLIF(value1, value2)When to Handle NULLs
- Whenever a column might contain missing data
- In aggregations (NULLs are ignored by COUNT, SUM, AVG, etc.)
- In comparisons (= NULL is always unknown, never true)
- In NOT IN subqueries (NULLs cause unexpected empty results)
Key Points
- IS NULL / IS NOT NULL — The only correct way to test for NULL. Never use = NULL.
- Three-Valued Logic — TRUE, FALSE, and UNKNOWN. NULL comparisons yield UNKNOWN.
- COALESCE — Replaces NULL with a fallback value.
- Aggregates Skip NULLs — COUNT(column) skips NULLs; COUNT(*) counts all rows.
- NOT IN Trap —
WHERE x NOT IN (1, 2, NULL)returns zero rows. Use NOT EXISTS instead. - NULLS FIRST/LAST — Control NULL position in ORDER BY.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
NULL Handling Best Practices Challenge
Write a query that find students without a grade.
Expected result
Students who have not been assigned a grade.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is NULL in SQL?
Initializing database...Each run starts from fresh sample data.
More Examples
Replace NULLs with COALESCE
Show a default value for missing grades.
Initializing database...Each run starts from fresh sample data.
Count NULLs vs non-NULLs
Compare total rows to non-NULL grade count.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Why does WHERE column = NULL not work?
Because NULL = NULL is UNKNOWN, not TRUE. SQL uses three-valued logic. Always use IS NULL or IS NOT NULL.
Does NULL equal empty string in PostgreSQL?
No. NULL and '' (empty string) are different in PostgreSQL. NULL means unknown/missing; '' is a known, empty value.