IS NULL in Oracle SQL PLSQL

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

Example 1:
Syntax to use IS NULL in IF statement is:
IF literal_name IS NULL THEN
<business_logic>
END IF;

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

Example 2:

Using IS 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 NULL, then we can achieve the same as:

SELECT *
FROM employee
WHERE commission IS NULL;

We will get the following results:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
107 Emp G 12000 Sales
108 Emp H 12000 Support

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


Tagged , , , , , , , , , , , . Bookmark the permalink.