Oracle IS NOT NULL condition.

Preamble

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

Syntax for IS NOT NULL condition in Oracle/PLSQL

expression IS NOT NULL

Parameters and arguments of the condition

• expression – An expression to check if it contains the value NOT NULL.

Note:

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

Example with the SELECT operator

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

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This example IS NOT NULL will return all entries from the customers table where the customer_name field does not contain the value NULL.

Example with the INSERT operator

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

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

This example IS NOT NULL will insert entries into the suppliers table, where the account_no field in the clients table does not contain the value NULL.

Example with UPDATE operator

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

UPDATE customers
SET status = 'Active'
WHERE customer_name IS NOT NULL;

This example IS NOT NULL will update the records in the customers table where the customer_name field does not contain the value NULL.

Example with DELETE operator

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

DELETE FROM customers
WHERE status IS NOT NULL;

This example IS NOT NULL will remove all entries from the customers table where the status field does not contain the value NULL.

Example of using PLSQL code in a block

You can use the Oracle IS NOT NULL condition in PLSQL to check if the value does not contain NULL.
For example:

IF variable IS NOT NULL then
...
END IF;

If the variable variable does not contain the value NULL, then “IF” expression will be calculated in TRUE.

In this Oracle tutorial you will learn how to check a value that contains 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.