Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 193

There's more...

We have seen that there is no performance improvement in using IN or EXISTS in our
queries. However, we cannot use one of them without considering the expected result.

Let's perform a small experiment:

1. Connect to the HR schema:

CONNECT hr@TESTDB/hr

2. Query for employees who are not managers using NOT IN :

SELECT COUNT(*) FROM hr.EMPLOYEES E
WHERE E.EMPLOYEE_ID NOT IN (
SELECT E2.MANAGER_ID FROM hr.EMPLOYEES E2
);

The result for this statement is as shown in the following screenshot:

The query replies that there are no employees (zero) who aren't managers.

3. Query for employees who are not managers using NOT EXISTS :

SELECT COUNT(*) FROM hr.EMPLOYEES E
WHERE NOT EXISTS (
SELECT NULL FROM hr.EMPLOYEES E2
WHERE E2.MANAGER_ID = E.EMPLOYEE_ID
);