totn SQL

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.

subscribe button Subscribe

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 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 SQL statement:

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

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

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