SQL IS NULL Condition: Syntax, Usage, and Examples
The SQL IS NULL condition helps you check if a column contains no value, meaning it’s undefined or missing. In relational databases, NULL represents the absence of data, not zero or an empty string.
How to Use SQL IS NULL
Use SQL IS NULL in your WHERE clause to find rows where a specific column has a NULL value.
sql
SELECT *
FROM users
WHERE email IS NULL;
You can apply IS NULL to any column where you expect missing data. It’s a condition—not a function—so it doesn’t require parentheses.
To check if a column has a value, use IS NOT NULL.
sql
SELECT *
FROM users
WHERE phone_number IS NOT NULL;
Use IS NULL SQL logic when filtering out incomplete entries, finding missing foreign keys, or validating cleaned data.
When to Use SQL IS NULL
Identify Incomplete Records
Use SQL IS NULL when you need to find records with missing data. For instance, identifying customers without a phone number or employees without a manager.
Handle Optional Relationships
In foreign key relationships, child tables might have NULL values if the relationship is optional. For example, products with no assigned supplier will show up with a NULL in the supplier_id column.
Clean and Validate Data
Use IS NULL SQL queries to catch records that need attention before exporting, updating, or analyzing the data.
Examples of SQL IS NULL in Action
Finding Missing Emails
Suppose you’re managing a mailing list and want to see which users haven’t provided their email address.
sql
SELECT user_id, name
FROM users
WHERE email IS NULL;
You can use IS NULL here to flag users who can’t be contacted via email.
Selecting Orphaned Records
If a project has been archived and no longer has an assigned team lead:
sql
SELECT project_id, name
FROM projects
WHERE team_lead_id IS NULL;
This helps pinpoint incomplete assignments in your workflow.
Filtering with IS NOT NULL
Now let’s say you only want users who do have a verified phone number:
sql
SELECT name, phone_number
FROM users
WHERE phone_number IS NOT NULL;
Use this version when you’re filtering for data that exists.
Learn More About SQL IS NULL
NULL Is Not Equal to Anything
A common mistake is trying to compare NULL with = or !=.
sql
-- WRONG
WHERE column = NULL
-- RIGHT
WHERE column IS NULL
You must use IS NULL or IS NOT NULL. The usual comparison operators won’t work with NULL.
SQL Server IS NULL Behavior
In SQL Server, the IS NULL operator behaves as expected in basic queries. But when it comes to sorting or joining, NULL values can complicate logic. For example, joins involving NULL values won’t match unless you account for them.
sql
SELECT *
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.customer_id IS NULL;
This type of query helps you find unmatched records—orders without customers.
IS NULL with CASE Statements
Use IS NULL in conditional logic to handle missing data explicitly:
sql
SELECT name,
CASE
WHEN email IS NULL THEN 'Email Missing'
ELSE 'Email Provided'
END AS email_status
FROM users;
This helps build user-friendly outputs or data summaries.
IS NULL with Aggregates
Let’s say you want to count how many products have a NULL value in the description column:
sql
SELECT COUNT(*)
FROM products
WHERE description IS NULL;
Use this approach to get statistics about missing values.
SQL Server IS NULL in Stored Procedures
If you’re writing stored procedures, be cautious when testing for NULL:
sql
CREATE PROCEDURE GetUserByEmail @email VARCHAR(100)
AS
BEGIN
SELECT *
FROM users
WHERE email = @email OR (email IS NULL AND @email IS NULL);
END;
This workaround accounts for both the column and input parameter possibly being NULL.
Using IS NULL in Joins
When performing an outer join, IS NULL helps identify unmatched rows. For instance, you might want to find customers who haven’t placed any orders:
sql
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
This pattern is useful for audit reports and quality checks.
Combining IS NULL with AND/OR Logic
Use IS NULL with AND or OR for multi-condition filtering:
sql
SELECT *
FROM products
WHERE price IS NULL OR description IS NULL;
This pulls any products missing key data fields.
Default Values to Handle NULLs
While IS NULL helps detect missing values, you can also handle them using COALESCE() or IS NULL() in SQL Server:
sql
SELECT name, COALESCE(email, 'No email provided') AS contact_email
FROM users;
This doesn’t change the database—it just displays a fallback value.
Filtering with Computed NULLs
Sometimes expressions result in NULL. If a division operation includes a NULL value, the result becomes NULL. You can filter those results out:
sql
SELECT id, score / attempts AS avg_score
FROM quiz_results
WHERE attempts IS NOT NULL;
This approach ensures you don’t divide by or with missing values.
NULL in Grouping and Reporting
NULLs are grouped together in aggregate functions. If you group by a nullable column, the NULL group will be treated as its own group:
sql
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
Any employees with no assigned department will fall under the NULL group.
Use IS NULL here to break out and analyze such rows separately if needed.
NULL Isn’t Always Bad
Sometimes NULL simply means “not applicable.” For example, a person’s middle name or a product’s expiration date might be left out intentionally.
You can design your queries to treat NULL differently based on its meaning in your context.
Use SQL IS NULL not just to clean up your data—but also to understand its shape, gaps, and intent. This small clause plays a big role in helping you write accurate, meaningful SQL queries.