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.

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.

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.

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:

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:

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 !=.

-- 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.

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:

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:

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:

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:

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:

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:

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:

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:

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.