SQL IS NULL / IS NOT NULL
beginnerfiltering
2 min read
What is IS NULL?
In SQL, NULL represents a missing or unknown value. You cannot check for NULL with = because NULL = NULL evaluates to unknown, not true. Use IS NULL and IS NOT NULL instead.
Syntax
SELECT * FROM table_name WHERE column IS NULL;
SELECT * FROM table_name WHERE column IS NOT NULL;When to Use
- Finding records with missing email addresses
- Filtering out incomplete data
- Checking for optional fields that have not been filled in
Key Points
- Never Use = NULL —
WHERE column = NULLalways returns no rows. Always useIS NULL. - Three-valued Logic — SQL uses three-valued logic: true, false, unknown. Any comparison with NULL yields unknown.
- COALESCE — Use
COALESCE(column, default)to replace NULLs with a default value in output. - NOT NULL Constraint — Columns defined as
NOT NULLcan never contain NULL values. - Aggregates and NULL — Most aggregate functions (SUM, AVG, COUNT(column)) ignore NULLs.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL IS NULL / IS NOT NULL Challenge
Write a query that retrieve students who have no email address on file.
Expected result
Students where the email column is NULL.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is IS NULL?
Initializing database...Each run starts from fresh sample data.
More Examples
Find employees with a manager
Retrieve employees whose manager_id is not null.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Why can I not use = to check for NULL?
In SQL, NULL represents an unknown value. Comparing anything to unknown yields unknown, not true or false. IS NULL is specifically designed to test for this.
How do NULLs affect COUNT?
COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values in that column.