tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

Oracle/PLSQL: IS NOT NULL Condition

Learn 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 Oracle IS NOT NULL Condition is:

expression IS NOT NULL

Parameters or Arguments

expression is the value to test.

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.

Learn how to test for a value that is null.