IS NULL Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

IS NULL Operator in Oracle with Examples

In this article, I am going to discuss IS NULL Operator in Oracle with Examples. Please read our previous article, where we discussed Real-Time Examples of SET Operators in Oracle with Examples. At the end of this article, you will understand IS NULL Operator in detail with Examples.

Is null Operator in Oracle:

The Is Null Operator is used for Comparing nulls in a table. NULL is unknown (or) undefined value in database, NULL != 0 & NULL != space. So, use the” IS ” keyword.

Syntax: where <column name> is null;

Example to Understand Is null Operator in Oracle

We are going to use the following Employee table to understand the IS NILL Operator in Oracle with examples.

Please use the below SQL Script to create the Employee table with the required data.

SET linesize 300;
DROP Table Employee;

CREATE TABLE Employee
(
 EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO Employee VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO Employee VALUES(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO Employee VALUES(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO Employee VALUES(7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO Employee VALUES(7654, 'MARTIN','SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO Employee VALUES(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO Employee VALUES(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO Employee VALUES(7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO Employee VALUES(7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO Employee VALUES(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO Employee VALUES(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO Employee VALUES(7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO Employee VALUES(7902, 'FORD','ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO Employee VALUES(7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Example: Write a query to display Employees whose commission is NULL?

SELECT * FROM Employee WHERE COMM IS NULL;

Once you execute the above query, you will get the following output.

Example: Write a query to display Employees whose commission is NOT NULL?

SELECT * FROM Employee WHERE COMM IS NOT NULL;

Once you execute the above query, you will get the following output.

Example: Write a query to display ename, job, sal, comm, and also sal+comm from Employee table whose ename is “SMITH”?

SELECT ENAME, JOB, SAL, COMM, COMM+SAL FROM Employee WHERE ENAME=’SMITH’;

Once you execute the above query, you will get the following output.

Note: If any arithmetic operator is performing some operation with NULL then it again returns NULL only.

Example:

If x=1000;

1. x+null ——-> 1000+null ———–> null
2. x-null ——-> 1000-null ————> null
3. x*null ——> 1000*null ————> null
4. x/null ——–> 1000/null ————> null

To overcome the above problem we should use a predefined function is called as “NVL()”

NVL(Exp1, Exp2):

NVL stands for NULL VALUE. It is a pre-defined function and is used to replace a user-defined value in place of NULL in the expression. This function is having two arguments i.e. Expression1 and Expression2. If Expression1 is null then returns Expression2value (user-defined value and If Expression1 is not null then returns Expression2 only.

Example:

SELECT NVL(NULL,0) FROM DUAL;

Output:

Example:

SELECT NVL(NULL,100) FROM DUAL;

Output:

Example:

SELECT NVL(0,100) FROM DUAL;

Output:

Example:

SELECT ENAME, JOB, SAL, COMM, NVL (COMM,0)+SAL FROM Employee WHERE ENAME=’SMITH’;

Output:

Nvl2 (Exp1, Exp2, Exp3) Function in Oracle:

It is a pre-defined function that is an extension of NVL () having 3 arguments are Exp1, Exp2, Exp3.

1. If Exp1 is Null ———–> Exp3 value (User Defined value)
2. If Exp1 is not null ——> Exp2 value (User Defined value)

Example:

Write a query to update all employee commissions in a table based on the following conditions?

1. IF employee comm is null then update those employees comm as 600.
2. IF employee comm is not null then update those employees comm as comm+500.

Solution:

UPDATE Employee SET COMM=NVL2 (COMM, COMM+500,600);

In the next article, I am going to discuss Functions in Oracle with Examples. Here, in this article, I try to explain IS NULL Operator in Oracle with Examples and I hope you enjoy this IS NULL Operator in Oracle with Examples article. If you have any queries regarding the Oracle IS NULL Operator, then please let us know by putting your query in the comment section.