intro
Let’s go through real-world examples to understand how the SQL IS NOT NULL condition helps you filter out entries based on NULL values.
NULL values can be tricky to handle in a database, which is why most systems provide special functions and operators to work with them. That is where the SQL IS NOT NULL condition comes in, allowing you to filter out rows where a column contains NULL.
Let’s dive in!
What Is IS NOT NULL in SQL?
IS NOT NULL is an SQL condition used to check whether a value, expression, or column does not contain a NULL value.
In SQL, NULL represents the absence of a value (“unknown” or “does not exist”) and cannot be compared using regular comparison operators like =. In fact, the result of NULL = NULL is unknown in SQL.
Because NULL is a unique value that requires special handling, the SQL IS NOT NULL condition helps exclude entries with unknown or missing data from your results, ensuring you work only with valid data.
IS NOT NULL SQL Condition: Syntax and Usage
The syntax for IS NOT NULL is simple:
1
expression IS NOT NULL
This checks whether the SQL expression (a column, hardcoded value, or other type of expression) is a NULL value. If the value of the expression holds NULL, the condition returns false. If it does not hold NULL, it returns true.
In most cases, the IS NOT NULL SQL condition is used in WHERE clauses, like this:
1
SELECT *
2
FROM table_name
3
WHERE column_name IS NOT NULL;
This returns all records from table_name where the column_name does not contain a NULL value.
Note: Like any other operator, IS NOT NULL can appear multiple times in the WHERE clause, applied to different columns and expressions.
SQL IS NOT NULL Examples
Now that you understand what the SQL IS NOT NULL clause is and how to use it, let’s see it in action with some real-world examples.
Note: The examples below will be executed against a MySQL server. However, they will be written in standard SQL so that you can execute them on other databases. We will use DbVisualizer as the database client to run the queries, explore the tables, and view the results.
Time to put IS NOT NULL to work!
Data Filtering
Suppose you are working with a customers table that contains the following data:
You are interested in retrieving all customers who do not have an email address. Achieve that using the following SELECT query:
1
SELECT *
2
FROM customers
3
WHERE email IS NOT NULL;
The output will be:
Note how Mike Brown was selected because his email address is empty but not NULL. On the other hand, Jane Smith was not selected because her email is NULL.
Similarly, you can apply the SQL IS NOT NULL condition multiple times to get all customers who have both a phone number and an email address:
1
SELECT *
2
FROM customers
3
WHERE phone IS NOT NULL and email IS NOT NULL;
This time, the result will be:
Join Filtering
Another common scenario where IS NOT NULL is useful is when performing a JOIN and you want to exclude records with missing data programmatically.
For example, let’s say you want to retrieve the number of orders placed by each customer who has an email address. In particular, the customers and orders tables are connected by a foreign key:
You can achieve your goal using the following GROUP BY query:
1
SELECT c.name, COUNT(o.order_id) AS order_count
2
FROM customers c
3
JOIN orders o ON c.id = o.customer_id
4
WHERE c.email IS NOT NULL
5
GROUP BY c.name;
The result includes only those customers who have an email address and have placed at least one order:
Calculations
Assume you have the following data in a products table:
Your goal is to calculate the final price for each product after applying the discount. A first attempt might look like this:
1
SELECT
2
name,
3
price,
4
discount,
5
price - (price * discount / 100) AS final_price
6
FROM products;
However, this produces NULL values for final_price where discount is NULL:
The reason for this output is that any arithmetic involving NULL in SQL results in NULL.
To fix this and exclude such products, add an IS NOT NULL SQL condition:
1
SELECT
2
name,
3
price,
4
discount,
5
price - (price * discount / 100) AS final_price
6
FROM products
7
WHERE discount IS NOT NULL;
This time, the result will be:
Great! The products with a NULL final price have been excluded.
Extra: Want to keep all rows and handle NULL discounts? Use the SQL COALESCE function to treat NULL as 0% discount:
1
SELECT
2
name,
3
price,
4
discount,
5
price - (price * COALESCE(discount, 0) / 100) AS final_price
6
FROM products;
In this case, if discount is NULL, it is treated as 0%, so the original price is returned as the final price:
Fantastic! You are now a master of SQL IS NOT NULL!
Conclusion
In this guide, you learned what IS NOT NULL is in SQL, how it works, and how to use it in real-world scenarios. As demonstrated here, DbVisualizer helps you to manage data visually and intuitively—whether it contains information or is NULL. This is just one of many powerful features offered by the database client. Others include SQL formatting, ERD-style schema diagrams, and query optimization tools. Download DbVisualizer for free today!
FAQ
What databases support IS NOT NULL?
IS NOT NULL is part of the ANSI SQL standard and is supported by all major relational database systems. That includes MySQL, PostgreSQL, SQL Server, and Oracle. The main difference between these databases lies in how they represent boolean values. In databases that do not support booleans, the result of IS NOT NULL is typically returned as 0 (false) or 1 (true). In databases that support boolean data types, the result is returned as true or false.
What is the opposite of IS NOT NULL?
The opposite of IS NOT NULL is IS NULL. While IS NOT NULL checks for values that are present, IS NULL filters for rows where the value is missing or unknown. IS NULL, as opposed to IS NOT NULL, is used to find records where a column explicitly contains a NULL value.
What is the difference between <> NULL, != NULL, and IS NOT NULL in SQL?
In SQL, <> NULL and != NULL are equivalent, but neither works as expected because NULL represents an unknown value. Any comparison with NULL—even NULL <> NULL—returns “unknown,” not “true” or “false.” To correctly check if a value is present, always use the IS NOT NULL condition.
How to optimize a query with an SQL IS NOT NULL condition?
To optimize a query with an IS NOT NULL SQL condition, make sure the column being checked is indexed. This helps the database quickly filter out rows without scanning the entire table. Additionally, avoid using IS NOT NULL directly in JOINs or where possible, combine it with other filtering conditions to minimize the dataset. Consider studying the query execution plans and dig into the EXPLAIN plan in your database to check performance and make adjustments, such as indexing or restructuring the query if necessary.
Why use a visual database client?
A visual database client like DbVisualizer makes it easier to recognize and query data. It improves readability, debugging, and understanding of complex data thanks to its rich, in-depth, advanced data exploration capabilities. Grab a 21-day free trial for DbVisualizer Pro to discover all its features!
