Sunday, February 08, 2009

Not In Vs Not Exists

The Not in and Not exists clauses are quite similar except for one major difference which can be illustrated using the example below:

EMP_NBR

EMP_NAME MGR_NBR
1 DON 5
2 HARI 5
3 RAMESH 5
4 JOE 5
5 DENNIS NULL
6 NIMISH 5
7 JESSIE 5
8 KEN 5
9 AMBER 5
10 JIM 5

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );


COUNT(*)
———-
0


SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9


Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back.