Basic about IS NULL Condition – using and example

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

Description

The IS NULL circumstance is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, in any other case it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Subscribe

Syntax

The syntax for the IS NULL condition in SQL is:

expression IS NULL

Parameters or Arguments

expression The expression to take a look at for a NULL value.

DDL/DML for Examples

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

Get DDL/DML

Example – Using IS NULL with the SELECT Statement

When trying out for a NULL value, IS NULL is the recommended assessment operator to use in SQL. Let’s start by means of looking at an instance that shows how to use the IS NULL condition in a SELECT statement.

In this example, we have a table called clients 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 document selected. These are the outcomes that you need to see:

customer_id last_name first_name favorite_website 8000 Anderson Paige NULL

This example will return all data from the customers desk where the favorite_website contains a NULL value.

Example – Using IS NULL with the UPDATE Statement

Next, let’s seem to be at an example of how to use the IS NULL situation in an UPDATE statement.

In this example, we have a desk referred to as 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 statistics 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 one hundred

This instance will update all category_id values in the products table to one hundred where the category_id contains a NULL value. As you can see, the category_id in the remaining row has been updated to a hundred

Example – Using IS NULL with the DELETE Statement

Next, let’s seem to be at an example of how to use the IS NULL situation in a DELETE statement.

In this example, we have a desk 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 facts from the orders desk 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 instance will delete all documents from the orders table where the customer_id carries a NULL value. As you can see, it deleted the report for order_id=5.