Oracle / PLSQL: IS NULL Condition

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

Description

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

Syntax

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

expression IS NULL

Parameters or Arguments

expression

The value to test whether it is a null value.

Note

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

Example - With SELECT Statement

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

SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.

Example - With INSERT Statement

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

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

This Oracle IS NULL example will insert records into the suppliers table where the city contains a null value.

Example - With UPDATE Statement

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

UPDATE suppliers
SET name = 'Apple'
WHERE name IS NULL;

This Oracle IS NULL example will update records in the suppliers table where the name contains a null value.

Example - With DELETE Statement

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

DELETE FROM suppliers
WHERE supplier_name IS NULL;

This Oracle IS NULL example will delete all records from the suppliers table where the supplier_name contains a null value.

Example - Using PLSQL Code

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

For example,

IF Lvalue IS NULL then

   ...

END IF;

If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.

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