Oracle IS NULL condition.

Preamble

The Oracle IS NULL condition is used to check the NULL value. You can use the IS NULL condition either in an SQL sentence or in a block of PLSQL code.

Syntax for IS NULL condition in Oracle/PLSQL

expression IS NULL

Parameters and arguments of the condition

• expression – An expression to be checked for the content of the NULL value.

Note:

• If the expression contains a value of NULL, then the condition is TRUE.
• If the expression does not contain a value of NULL, then the condition assumes a value of FALSE.

Example with the SELECT operator

Below is an example of how to use the Oracle IS NULL condition in a SELECT query:

SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

This example IS NULL will return all entries from the suppliers table where the field supplier_name contains the value NULL.

Example with the INSERT operator

Below is an example of how to use the Oracle IS NULL condition in the INSERT operator:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;

This example IS NULL will insert entries in the table where the city field contains the value NULL.

Example with UPDATE operator

Below is an example of how to use Oracle condition IS NULL in UPDATE operator:

UPDATE suppliers
SET name = 'Apple'
WHERE name IS NULL;

This example IS NULL will update the entries in the suppliers table where the name field contains the value NULL.

Example with DELETE operator

Below is an example of how to use the Oracle IS NULL condition in DELETE:

DELETE FROM suppliers
WHERE supplier_name IS NULL;

This example IS NULL will remove all entries from the suppliers table where the field supplier_name contains the value NULL.

Example use of PLSQL code in a block

You can use the Oracle IS NULL condition in PLSQL to check if the value contains NULL. For example:

IF variable IS NULL then
...
END IF;

Will calculate the expression’s “IF” condition as TRUE if the variable holds a NULL value.

In this Oracle tutorial you will learn how to check a value that does not contain NULL.

Oracle Tutorial; is NULL and is NOT NULL

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.