Using IS NOT NULL Condition in Oracle

This article is written about how to use the Oracle IS NOT NULL circumstance with syntax and examples.

Description

The Oracle IS NOT NULL circumstance is used to take a look at for a NOT NULL value. You can use the Oracle IS NOT NULL condition in either a SQL assertion or in a block of PLSQL code.

Syntax

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

expression IS NOT NULL

Parameters or Arguments

expression The cost to check whether it is a not null value.

Note

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

Example – With SELECT Statement

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

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will return all documents from the customers desk the place the customer_name does not comprise a null value.

Example – With INSERT Statement

Here is an instance of how to use the Oracle IS NOT NULL circumstance 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 documents into the suppliers desk the place the account_no does not include a null value in the customers table.

Example – With UPDATE Statement

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

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

This Oracle IS NOT NULL example will replace files in the customers desk the place the customer_name does no longer incorporate a null value.

Example – With DELETE Statement

Here is an instance 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 data from the customers desk where the reputation does no longer include a null value.

Example – Using PLSQL Code

You can use the Oracle IS NOT NULL condition in PLSQL to take a look at if a price is no longer 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 article is written about how to test for a fee that is null.