ORACLE9i-study notebook-3

Null Values in a subquery

SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN
                              (SELECT  mgr.manager_id
                               FROM    employees mgr);
no rows selected

The SQL statement in the slide attempts to display all the employees who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any lows. One of the values returned by the inner query is a null value, and hence the entire query teturns no rows. The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator. The NOT IN operator is equivalent to <> ALL.
Notice that the null value as part of the results set of a subquery is not a problem if you use the IN operator. The IN operator is equivalent to =ANY. For example, to display the employees who have subordinates, use the following SQL statement:
SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id IN
                          (SELECT  mgr.manager_id
                           FROM    employees mgr);
Alternatively, a WHERE clause can be included in the subquery to display all the employees who do not have any subordinates:
SELECT last_name FROM  employees
WHERE  employee_id NOT IN
                          (SELECT  manager_id FROM  employees
                           WHERE   manager_id IS NOT NULL);