NULL Semantics and IS NULL in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this WHERE Clause and Comparison Operators
Builds toward Boolean Logic in WHERE (AND, OR, NOT), Aggregate Functions (COUNT, SUM, AVG, MIN, MAX), LEFT JOIN and RIGHT JOIN, COALESCE and NULLIF
What is IS NULL in SQL?
NULL is SQL's way of marking a value as missing.
In real databases, missing data is everywhere. A customer signed up but never filled in their city. An employee was just hired and doesn't have a manager yet. A session started but hasn't ended. None of those are blank strings or zeros. The data simply isn't there. SQL stores that absence as NULL.
How do you find rows where a column is NULL?
Knowing how to handle NULL is essential for any analyst working with real data. The gaps show up constantly: incomplete customer profiles, optional fields, records that are still in-progress. When you need to find those rows, or exclude them, you use IS NULL and IS NOT NULL.
To find rows where a column has no value:
SELECT name, email
FROM customers
WHERE city IS NULLSQL checks each row in customers. If city has no value recorded, the row passes. You get every customer with a missing city.
How do you filter out NULLs with IS NOT NULL?
The inverse works the same way:
SELECT name, price, price * 0.9 AS clearance_price FROM products WHERE category_id IS NULL
Swap IS NULL for IS NOT NULL when you want only rows that have a value — filtering out anything that's incomplete or uncategorized.
Why does WHERE column = NULL return no rows?
The one thing that trips people up: WHERE city = NULL never returns anything.
When SQL tries to compare a column to NULL using =, it can't produce a true or false answer. An unknown value compared to anything gives an unknown result. WHERE drops rows where the condition isn't clearly true. So = NULL fails silently, every time, for every row. IS NULL works because it doesn't try to compare. It asks a direct question: is this value absent? That always has a clear answer.
A 'customers' table has a 'city' column that is sometimes NULL. Which condition returns only rows where city is missing?
Practice IS NULL in SQL
Brightlane's data quality team is auditing incomplete customer profiles ahead of a CRM migration to a new system.
Write a query to return the name and email of every customer whose city has not been recorded.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - Some customers have a recorded
cityvalue; others havecityset toNULL. - A missing city is stored as
NULL, not as an empty string.
Output:
- One row per customer with no city on file, with columns
nameandemail.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 IS NULL practice problems
Write a query to return the name and email of every customer whose city has not been recorded.
Write a query to return the name and title of every such employee.
Write a query to return the ID and start time of every open session.
Write a query to return each uncategorized product's name, its original price, and its clearance price.
Write a query to return the name and title of every qualifying employee.
Write a query to return the ID and name of every root category.
Write a query to return the ID, user ID, and start time of every session that has ended.
Write a query to return the ID of every such order.
Write a query to return the ID and name of every customer with a recorded city value.
Start learning to practice all 9 IS NULL problems, with instant grading and mastery tracking.
Common questions about IS NULL
Is NULL the same as an empty string or zero?
No. An empty string is a value you can compare and sort, and zero is a number. NULL is the absence of a value, and PostgreSQL answers IS NULL with false for both of the others. Treating them as interchangeable is how missing data ends up counted as real data.
Does NULL equal NULL in SQL?
No, and it does not differ from it either. Comparing NULL with anything, NULL included, produces NULL rather than true or false. That is why IS NULL exists: it asks whether a value is absent instead of trying to compare it with something.
Does a WHERE clause keep a row when the condition is NULL?
No. WHERE keeps a row only when the condition comes out true, so a condition that evaluates to NULL drops the row exactly as a false one does. The two are different inside SQL and identical in the result you get back.
How do you count the rows that are missing a value?
Filter with IS NULL and count what is left. Counting with an equals comparison against NULL returns zero every time, because that comparison is never true, which makes a real gap in the data look like no gap at all.
