totn SQL

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:

Try It
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 techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following UPDATE statement:

Try It
UPDATE customers
SET favorite_website = 'techonthenet.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 techonthenet.com
5000 Smith Jane techonthenet.com
6000 Ferguson Samantha techonthenet.com
7000 Reynolds Allen techonthenet.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

This example will update all favorite_website values in the customers table to 'techonthenet.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:

Try It
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.