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.