Oracle / PLSQL: IS NOT NULL Condition

This Oracle tutorial explains how to use the Oracle IS NOT NULL condition with syntax and examples.

Description

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

Syntax

The syntax for the IS NOT NULL condition in Oracle/PLSQL is:

expression IS NOT NULL

Parameters or Arguments

expression

The value to test whether it is a not null value.

Note

• If expression is NOT a NULL value, the condition evaluates to TRUE.
• If expression is a NULL value, the condition evaluates to FALSE.

Example - With SELECT Statement

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

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will return all records from the customers table where the customer_name does not contain a null value.

Example - With INSERT Statement

Here is an example of how to use the Oracle IS NOT NULL condition in an INSERT statement:

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

This Oracle IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a null value in the customers table.

Example - With UPDATE Statement

Here is an example of how to use the Oracle IS NOT NULL condition in an UPDATE statement:

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

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

Example - With DELETE Statement

Here is an example of how to use the Oracle IS NOT NULL condition in a DELETE statement:

DELETE FROM customers
WHERE status IS NOT NULL;

This Oracle IS NOT NULL example will delete all records from the customers table where the status does not contain a null value.

Example - Using PLSQL Code

You can use the Oracle IS NOT NULL condition in PLSQL to check if a value is not null.

For example:

IF Lvalue IS NOT NULL then

   ...

END IF;

If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.

This Oracle tutorial explains how to test for a value that is null.