SQL IS NOT NULL Condition: Syntax, Usage, and Examples

The SQL IS NOT NULL condition checks whether a column contains a value rather than being empty. In SQL, a NULL represents missing or undefined data—not the number zero, an empty string, or false. Use IS NOT NULL to return rows where values are present and usable.

How to Use SQL IS NOT NULL

Use SQL IS NOT NULL in a WHERE clause to filter out records that contain empty or missing values in a column.

SELECT *
FROM users
WHERE phone_number IS NOT NULL;

You can include IS NOT NULL alongside other filtering conditions with AND or OR operators. The IS NOT NULL SQL check ensures you're working with rows that contain real data and avoids processing empty or irrelevant fields.

You should also use this condition when comparing values that could be empty, as comparison operators like = and != don't behave correctly with NULL.

When to Use SQL IS NOT NULL

Filter Out Incomplete Records

Use this condition to ignore rows with missing data. For example, if you're sending a promotional email, you'll want to exclude users who haven't shared their email addresses.

Avoid Calculation Errors

If you're running calculations on numeric fields, it's crucial to remove NULL values first to avoid incorrect averages, totals, or divisions by zero.

Isolate Active or Filled Entries

Use IS NOT NULL to return data only from rows that have been filled in, marked, or activated—such as accounts with last login dates or employees with a recorded salary.

Examples of SQL IS NOT NULL

Query Users with Valid Emails

Use this condition to retrieve users who have submitted a valid email address.

SELECT name, email
FROM users
WHERE email IS NOT NULL;

This is useful when cleaning lists or targeting users for newsletters.

Pull Orders with Associated Customer IDs

You can return only the orders that are linked to a customer:

SELECT order_id, customer_id
FROM orders
WHERE customer_id IS NOT NULL;

Orders without a customer ID might be incomplete or part of a failed transaction.

Join with a Filter for Existing Data

When joining two tables, you may want to skip rows from the left table that have no match in the right table:

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NOT NULL;

This query filters out employees not assigned to any department.

Learn More About SQL IS NOT NULL

Comparing NULL vs Non-NULL Values

A NULL value behaves differently than other values. You can’t use regular comparison operators like = or != on NULL. That’s why IS NOT NULL and IS NULL exist.

-- Wrong
SELECT * FROM products WHERE price != NULL;

-- Right
SELECT * FROM products WHERE price IS NOT NULL;

Use IS NOT NULL whenever you're testing the existence of a value—not just its specific content.

SQL Server IS NOT NULL

In SQL Server, you can use IS NOT NULL inside WHERE, CASE, JOIN, and HAVING clauses. The syntax is the same as in other databases:

SELECT *
FROM employees
WHERE hire_date IS NOT NULL;

You can also pair it with the ISNULL() function if you want to display a placeholder when a value is missing but still filter the valid ones:

SELECT name, ISNULL(phone_number, 'Not Provided') AS contact_number
FROM customers
WHERE phone_number IS NOT NULL;

This retrieves rows with phone numbers and replaces any potential null display with a default string.

Working with Conditional Logic

You can use IS NOT NULL inside CASE expressions to show different results based on whether a column has a value:

SELECT name,
  CASE
    WHEN manager_id IS NOT NULL THEN 'Has Manager'
    ELSE 'No Manager'
  END AS manager_status
FROM employees;

This provides clean, readable summaries in reports or dashboards.

Use with Aggregates

When calculating totals or averages, make sure you exclude NULL values. Most aggregate functions automatically skip NULLs, but you might still want to filter the records beforehand:

SELECT AVG(salary)
FROM employees
WHERE salary IS NOT NULL;

Explicit filtering helps you double-check that your dataset is clean before analysis.

Chaining with Other Conditions

You can combine the IS NOT NULL logic with other conditions for more granular filtering:

SELECT *
FROM events
WHERE start_time IS NOT NULL AND event_type = 'Webinar';

This query finds webinars that have a defined start time.

Use in Joins for Data Validation

Sometimes, you want to verify which rows from a LEFT JOIN have valid matches in the joined table. Use IS NOT NULL to isolate those matches:

SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NOT NULL;

This finds only the orders tied to real customer records.

Filtering Based on User Input

Imagine you’re building a dynamic SQL query where a user might provide input, or leave it blank. If you receive a non-null parameter, you can include it:

-- Assuming @email is a parameter
SELECT *
FROM users
WHERE (@email IS NULL OR email = @email);

To reverse it and search only when the email exists:

WHERE (@email IS NOT NULL AND email = @email);

This condition helps support both strict and optional filtering logic.

Comparing IS NULL and IS NOT NULL in CASE

Here’s a deeper example that compares both conditions in a single CASE expression:

SELECT name,
       CASE
         WHEN phone_number IS NULL THEN 'Missing Phone'
         WHEN phone_number IS NOT NULL THEN 'Has Phone'
       END AS phone_status
FROM contacts;

By explicitly using both conditions, you show that you're handling NULL values deliberately.

IS NOT NULL with GROUP BY and HAVING

You can also use it in grouping queries to remove null groups:

SELECT department_id, COUNT(*)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING COUNT(*) > 5;

This pulls departments with more than five employees—excluding unassigned ones.

Validate Before Inserting or Updating

Before inserting or updating values into your table, use IS NOT NULL in a subquery to filter the valid references:

INSERT INTO reports (employee_id)
SELECT id
FROM employees
WHERE email IS NOT NULL;

This way, you avoid linking reports to unverified or incomplete employee records.

Use SQL IS NOT NULL to make sure your data has value before you trust, process, or display it. Whether you're writing reports, cleaning up inputs, or running analytics, this condition helps you focus only on what's there—because sometimes, what’s missing matters just as much as what’s present.