In SQL, we can use the IS NOT NULL operator to filter out the results from a database table where the value of a specific column is not null. This can help to ensure that you get a clean data that does not contain any missing values which can cause problems especially in functions that do not handle NULL values.
A NULL value refers to the absence of data which does not incorporate the empty strings, numerical zero, or NaN.
In this tutorial, we will learn how we can use the IS NOT NULL operator to filter out the results that contain NULL values in a given query.
Syntax:
We can express the basic syntax of the IS NOT NULL condition as shown in the following code snippet:
FROM table_name
WHERE column_name IS NOT NULL;
We start with the “select” keyword and specify the columns that we wish to retrieve in the result set followed by the name of the table from which we wish to fetch the data.
Next, we use the WHERE clause to introduce the data filtering based on a specific column. Finally, we specify the condition on which we wish to filter.
Example 1:
Let us look at some examples on how we can use the IS NOT NULL condition to filter out the results.
Consider the Sakila sample database which is available freely for download on the official MySQL page.
Suppose we wish to retrieve the name of customers whose “last_name” is not equal to null. We can use the query as shown in the following to accomplish this:
*
from
customer c
where
last_name is not null;
In this case, the query returns all the rows from the customer table where the value of the “last_name” column is not null.
Example 2: AND and OR Operators
We can combine the IS NOT NULL condition with other conditions using the AND and OR operators. This creates a more granular filtering.
For example, suppose we want to retrieve the customers whose “last_name” is not null and whose first name is Nancy or Holly.
We can use a query as demonstrated in the following:
customer_id,
first_name,
last_name,
FROM
customer
WHERE
last_name IS NOT NULL
AND (first_name = 'Nancy'
OR first_name = 'Holly');
In this query, we are combining the IS NOT NULL condition with the AND and OR operators to filter out the records where the last name is not null or the first name is equal to either Nancy or Holly.
The resulting output is as follows:
As you can see, this provides a way of performing a more granular and miniscule filtering on the target data.
Example 3: Using the Aggregate Functions
We can also use the IS NOT NULL function in conjunction with the SQL aggregate functions. For example, we can use it with the count() function to count the number of non-null values in a given column.
For example, suppose we want to determine the number of customers with non-null email addresses. We can use the query as follows:
COUNT(customer_id) AS total
FROM
customer
WHERE
email IS NOT NULL;
This should return the number of non-null values in the column as a numerical value as follows:
-----+
599|
Conclusion
In this tutorial, we learned all about the IS NOT NULL condition in SQL to filter out the null values from a given result set or database table. We also learned how we can create a more complex filtering by combining the IS NOT NULL condition with other conditions using the AND and OR operators.
