fastread homefastrread library fastread menu

Oracle/PLSQL : IS NOT NULL Condition

Tutorial by:Sweta      Date: 2016-04-15 00:39:54

❰ Previous Next ❱

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.

 

❰ Previous Next ❱


Oracle/PLSQL

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website