In Oracle SQL / PLSQL IS NOT NULL is used to check whether the value of a literal IS NOT NULL or not.

Example 1:
Syntax to use IS NOT NULL in IF statement is:

IF literal_name IS NOT NULL THEN

Here if the literal_name does not have NULL value then the IF condition will evaluate to TRUE, then the “THEN” section of the code will get executed.

Example 2:

Using IS NOT NULL is Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we want to see the records having commission as NOT NULL, then we can achieve the same as:

[sourcecode language=”sql”]
FROM employee
WHERE commission IS NOT NULL;[/sourcecode]

We will get the following results:

Employee_ID Employee_Name Salary Department Commission
102 Emp B IT 20000 20
103 Emp C IT 28000 20
104 Emp D Support 30000 5
105 Emp E Sales 32000 10
106 Emp F Sales 20000 5

Here we can see that we have successfully retrieved records having NOT NULL commission using IS NOT NULL in Oracle SQL PLSQL SELECT statement.

Leave a Reply

Your email address will not be published. Required fields are marked *