Databases Reference
In-Depth Information
The result is as shown in the following screenshot:
The query shows that there are 89 employees who aren't managers.
What happens now? We have two equivalent queries showing
different results?
No errors: simply the
IN
and
EXISTS
behave differently when we consider
NULL
values. Let's modify the query in step 2.
4. A modified version of the query in step 2 would look similar to the following:
SELECT COUNT(*) FROM hr.EMPLOYEES E
WHERE E.EMPLOYEE_ID NOT IN (
SELECT E2.MANAGER_ID FROM hr.EMPLOYEES E2
WHERE E2.MANAGER_ID IS NOT NULL
);
We have added the condition that
MANAGER_ID
is not null in our subquery.
The results of the modified query are shown in the following screenshot:
The result is now equivalent (and correct); it replies that there are 89
employees who aren't managers in our table.
It's very important to remember this difference when using
(NOT)
IN
queries instead
of
(NOT)
EXISTS
, which isn't affected by the same problem.
