SQL: IS NULL Condition

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

Description

The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a 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 NULL condition in SQL is:

expression IS NULL

Parameters or Arguments

expression

The expression to test for a 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 NULL with the SELECT Statement

When testing for a NULL value, IS 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 NULL condition in a SELECT 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 SQL statement:

SELECT *
FROM customers
WHERE favorite_website IS NULL;

There will be 1 record selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
8000	Anderson	Paige	NULL

This example will return all records from the customers table where the favorite_website contains a NULL value.

Example - Using IS NULL with the UPDATE Statement

Next, let's look at an example of how to use the IS NULL condition in an UPDATE 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 UPDATE statement:

UPDATE products
SET category_id = 100
WHERE category_id IS NULL;

There will be 1 record updated. Select the data from the products table again:

SELECT * FROM products;

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
7	Kleenex	100

This example will update all category_id values in the products table to 100 where the category_id contains a NULL value. As you can see, the category_id in the last row has been updated to 100.

Example - Using IS 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 NULL;

There will be 1 record 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
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20

This example will delete all records from the orders table where the customer_id contains a NULL value. As you can see, it deleted the record for order_id=5.