Hi Maran Firstly

It’s not about the difference in performance only because they are not the same when dealing with nulls in the data.

for example, we want to check how many employees are not managers of other employees by using NOT IN

SELECT COUNT (*)
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES);

the result will be :

COUNT(*)
----------
0

This means all employees are managers in this case.

let’s then take the same example but using NOT EXISTS

SELECT COUNT (*)
FROM EMPLOYEES EMP_1
WHERE NOT EXISTS
(SELECT NULL
FROM EMPLOYEES EMP_2
WHERE EMP_2.MANAGER_ID = EMP_1.EMPLOYEE_ID);

the result will be :

COUNT(*)
----------
89

So here, out of 101 employees, there are 89, not managers.

The most crucial thing is NULL represented in the manager_id column for the first employee 101 (Steven King). So wherever null is there while using NOT IN or IN, the evaluation of the inner query will be either FALSE or NULL and will return no records.

So now, when it comes to performance, it depends on the amount of data that both subquery and the outer query returns. If they are small, then IN is typically more appropriate. And vise versa. But remember, we have assumed that there are no nulls in the subquery result.

Regards