In Oracle PL/SQL, you may use the IS NOT NULL condition combined with a subquery in the DML (SELECT, INSERT, UPDATE, DELETE) statement. In IS NOT NULL condition, it is used to test for the NOT NULL value of a specific column or variable. If the NOT NULL value is found, it will return TRUE. Otherwise, it will return FALSE.

In this tutorial, you will learn how to use the "IS NOT NULL" condition In Oracle PL/SQL statement. We will walkthrough the "IS NOT NULL" syntax with detailed explanations and examples.

Syntax

The syntax of a the "IS NOT NULL" condition is as follows:

---

Parameters Explanations

column_name

table_name

expression

---

Examples - How to use the "IS NOT NULL" Condition with the SELECT Statement

In this tutorial, we will use the well-known Northwind sample database for demonstration purpose. Consider we have the following tables:

employees table with the following data:

id	last_name	first_name	notes
1	Freehafer	Nancy	[NULL]
2	Cencini	Andrew	Joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales.
3	Kotas	Jan	Was hired as a sales associate and was promoted to sales representative.
4	Sergienko	Mariya	[NULL]
5	Thorpe	Steven	Joined the company as a sales representative and was promoted to sales manager. Fluent in French.
6	Neipper	Michael	Fluent in Japanese and can read and write French, Portuguese, and Spanish.
7	Zare	Robert	[NULL]
8	Giussani	Laura	Reads and writes French.
9	Hellung-Larsen	Anne	Fluent in French and German.

Here is the example of selecting the employees table with the IS NULL condition



The result is:

id	last_name	first_name	notes
2	Cencini	Andrew	Joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales.
3	Kotas	Jan	Was hired as a sales associate and was promoted to sales representative.
5	Thorpe	Steven	Joined the company as a sales representative and was promoted to sales manager. Fluent in French.
6	Neipper	Michael	Fluent in Japanese and can read and write French, Portuguese, and Spanish.
8	Giussani	Laura	Reads and writes French.
9	Hellung-Larsen	Anne	Fluent in French and German.

In the above example, since the condition is the select only those records with column notes is not null. so only the records with notes which do NOT have null value has been returned.

Examples - How to use the "IS NOT NULL" Condition with the UPDATE Statement

Now, we can explore how to use the IS NOT NULL condition in the UPDATE statement. Consider to reuse the employees table again.

Now, if we issue the following SQL statement:



Then, if you run the selection again.


The result would be:

id	last_name	first_name	notes
2	Andrew	Andrew	Joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales.
3	Jan	Jan	Was hired as a sales associate and was promoted to sales representative.
5	Steven	Steven	Joined the company as a sales representative and was promoted to sales manager. Fluent in French.
6	Michael	Michael	Fluent in Japanese and can read and write French, Portuguese, and Spanish.
8	Laura	Laura	Reads and writes French.
9	Anne	Anne	Fluent in French and German.