SQL: IS NOT NULL Condition

This SQL tutorial explains how to use the SQL IS NOT NULL condition with syntax and examples.

Description

The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IS NOT NULL condition in SQL is:

expression IS NOT NULL

Parameters or Arguments

expression

The expression to test for a NOT NULL value.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using IS NOT NULL with the SELECT Statement

When testing for a non-NULL value, IS NOT NULL is the recommended comparison operator to use in SQL. Let's start by looking at an example that shows how to use the IS NOT NULL condition in a SELECT statement.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25

This example will return all records from the products table where the customer_id does not contain a NULL value.

Example - Using IS NOT NULL with the UPDATE Statement

Next, let's look at an example of how to use the IS NOT NULL condition in an UPDATE statement.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following UPDATE statement:

UPDATE customers
SET favorite_website = 'google.com'
WHERE favorite_website IS NOT NULL;

There will be 5 records updated. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	google.com
6000	Ferguson	Samantha	google.com
7000	Reynolds	Allen	google.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

This example will update all favorite_website values in the customers table to 'google.com' where favorite_website contains a NULL value. As you can see, the favorite_website has been updated all in but 1 row.

Example - Using IS NOT NULL with the DELETE Statement

Next, let's look at an example of how to use the IS NULL condition in a DELETE statement.

In this example, we have a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

Enter the following DELETE statement:

DELETE FROM orders
WHERE customer_id IS NOT NULL;

There will be 4 records deleted. Select the data from the orders table again:

SELECT * FROM orders;

These are the results that you should see:

order_id	customer_id	order_date
5	NULL	2016/05/01

This example will delete all records from the orders table where the customer_id does not contain a NULL value.