Basic about IS NOT NULL Condition – using and example

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

Description

The IS NOT NULL condition is used in SQL to take a look at for a non-NULL value. It returns TRUE if a non-NULL cost is found, in any other case it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

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

expression IS NOT NULL

Parameters or Arguments

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

DDL/DML for Examples

If you favor to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your very own database!

Get DDL/DML

Example – Using IS NOT NULL with the SELECT Statement

When trying out for a non-NULL value, IS NOT NULL is the endorsed comparison operator to use in SQL. Let’s begin through looking at an example that shows how to use the IS NOT NULL condition in a SELECT statement.

In this example, we have a desk known 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 SQL statement:

Try It

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 documents selected. These are the consequences that you have to 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 files from the products table the place the customer_id does no longer contain a NULL value.

Example – Using IS NOT NULL with the UPDATE Statement

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

In this example, we have a desk 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 UPDATE statement:

Try It

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

There will be 5 files updated. Select the data from the clients 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 instance will update all favorite_website values in the clients desk to ‘techonthenet.com’ the place favorite_website consists of a NULL value. As you can see, the favorite_website has been up to date 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 known as 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 four records deleted. Select the records 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 data from the orders table where the customer_id does no longer contain a NULL value.