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