NOT IN Condition and NULL Data

Before getting into discussions, lets start with a SQL example.
Trying to fetch all employees who are not manager!

Employee table Structure

Name Null Type
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

DATA for below column

EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7788 SCOTT 7566
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7934 MILLER 7782

Let run the SQL using NOT IN (ALL EMP NOT IN MGR).
SQL> select ename, job, mgr from emp where empno not in (select mgr from emp);
no rows selected

For no rows in output there can be two reasons
1. All employee are manager, just like in real professional life! 😉
but it is hypothetical as at least one employee will run the show i.e. doing works of manager 🙂

To understand second and correct reason, let run SQL with some rewrite.
Rewriting above SQL using Correlation and NOT EXISTS Condition.

SQL> select ename, job, mgr from emp a where not exists (select 1 from emp b where b.mgr= a.empno);

ENAME JOB MGR
---------- --------- ----------
SMITH CLERK 7902
ALLEN SALESMAN 7698
WARD SALESMAN 7698
MARTIN SALESMAN 7698
TURNER SALESMAN 7698
ADAMS CLERK 7788
JAMES CLERK 7698
MILLER CLERK 7782
8 rows selected.

Surprise!! How come query rewrite has caused a return of data as logically first SQL conditions was also correct, will come to that!
let rerun the first SQL with one more condition (Not Null) as below

In sub query only selecting those managers which has some information (Not NULL).

SQL> select ename, job, mgr from emp where empno not in (select mgr from emp where mgr is not null);

ENAME JOB MGR
---------- --------- ----------
SMITH CLERK 7902
ALLEN SALESMAN 7698
WARD SALESMAN 7698
MARTIN SALESMAN 7698
TURNER SALESMAN 7698
ADAMS CLERK 7788
JAMES CLERK 7698
MILLER CLERK 7782

8 rows selected.

After adding NOT NULL conditions, require data got returned.

The answer to above lies in NOT IN Conditions evaluation Logic. NOT IN, will try to evaluate with each and every record in set with “AND” condition.
For example,

Col1 NOT IN (1,2,3,4) will be same as Col1 != 1 AND Col1 !=2 AND Col3!=3 AND Col4!=4

Hence if NULL is part of set, complete set will evaluate to NULL neither TRUE nor FAlSE!!

i.e Col1 NOT IN (1,2,3,4,NULL) will be Col1 != 1 AND Col1 !=2 AND Col3!=3 AND Col4!=4 AND Col!=NULL

Anything not equal to NULL is NULL so whole expression was evaluated to NULL because of AND condition !!

Hence the addition of condition “Mgr is not null” result in avoiding NULL (Employee whose manager column is NULL) and ultimately correct result were returned.

As contrary with NOT IN, IN Conditions is evaluated as below.

Col1 in (1,2,3) will be same as Col1 = 1 OR Col1 = 2 OR Col1 = 3.

Due to OR Condition it can handle NULL in SET, below SQL will return valid data though sub query return NULL.

SQL> select ename, job, mgr from emp where empno in (select mgr from emp);

ENAME JOB MGR
---------- --------- ----------
JONES MANAGER 7839
BLAKE MANAGER 7839
CLARK MANAGER 7839
SCOTT ANALYST 7566
KING PRESIDENT
FORD ANALYST 7566
6 rows selected.

So next time writing SQL comprise of NOT IN, make sure to consider above behaviour and have logic to handle NULL Data.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

2 Responses to NOT IN Condition and NULL Data

  1. Palash says:

    Excellent article.Cleared my doubt

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s