NULL isn’t zero or empty—it’s unknown. Learn how to query with NULLs correctly.
If you're new to SQL, handling NULL
values can be one of the more confusing concepts to grasp. Unlike zero or an empty string, NULL
means “unknown” or “missing.” This seemingly simple idea can have a huge impact on how queries behave.
In this guide, we’ll explain what NULL
is, how it affects comparisons and queries, and how to work with it correctly. Whether you're writing your first query or debugging strange results, understanding how to use NULL
properly is essential.
In SQL, NULL
represents a missing or undefined value. It doesn't mean zero or empty—it means the value is not known or hasn’t been provided.
For example:
INSERT INTO users (name, email) VALUES ('Alice', NULL);
In this case, the email value for Alice is unknown. It's not an empty string, it’s explicitly NULL
.
This is where things get tricky. NULL
is not equal to anything—not even another NULL
. That’s why this condition fails:
SELECT * FROM users WHERE email = NULL;
-- returns nothing
To compare NULL
values, you must use:
IS NULL
IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
Want to practice these? You can use the Galaxy SQL Editor to run examples in a browser with no setup.
When you use logical operators (=
, !=
, AND
, OR
) or functions like COUNT()
with NULL
, behavior can vary.
COUNT(column)
ignores NULL
values.COUNT(*)
counts all rows, including rows where the column is NULL
.SELECT COUNT(email) FROM users; -- excludes NULLs
SELECT COUNT(*) FROM users; -- includes all rows
The COALESCE()
function returns the first non-NULL value in a list. It’s commonly used to replace NULL
values with a default.
SELECT name, COALESCE(email, 'no-email@example.com') AS contact_email
FROM users;
This ensures that every user has a fallback email address in the result.
Other similar functions:
IFNULL(a, b)
(MySQL)NVL(a, b)
(Oracle)When you group by a column that contains NULL
, those rows form a separate group.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
If some employees don’t have a department, they’ll be grouped under NULL
.
For more clarity on how this interacts with query results, read our guide to GROUP BY vs ORDER BY.
Use IS NULL
and IS NOT NULL
to include or exclude missing values.
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;
These are especially helpful in reporting, data cleanup, and debugging missing data.
You can use CASE
to handle NULL
logic in custom outputs:
SELECT name,
CASE
WHEN email IS NULL THEN 'Missing Email'
ELSE email
END AS email_status
FROM users;
This will label users without email addresses clearly in your result set.
User signups often don’t include every field. NULL
helps you track what’s missing.
Query for NULL
values to find where data pipelines or forms failed.
Use COALESCE
to plug holes in your data for better reporting or presentation.
Want more examples like these? Explore our SQL Learning Center and our Common SQL Errors Guide.
=
or !=
to compare with NULL
. Always use IS NULL
or IS NOT NULL
.NULL
keys can cause rows to disappear.COALESCE
or CASE
to handle missing values explicitly in outputs.NULL
so you don’t misinterpret results.Working with NULL
values is a critical skill in SQL that separates beginner mistakes from clean, production-ready queries. Knowing how to check, replace, and work around NULL
will help you avoid errors and write more effective reports.
Want to try your own IS NULL
and COALESCE
queries? Launch the Galaxy SQL Editor and write safe, schema-aware SQL in seconds.