SQL & PL/SQL

Below is a multiple choice question for preparing candidates for the exam
Introduction to Oracle SQL (1z0-007)

Evaluate this SELECT statement:

SELECT employee_id, name
FROM employee
WHERE employee_id NOT IN ( SELECT employee_id
FROM employee
WHERE department_id = 30
AND job = 'CLERK');

What would happen if the inner query returned NULL?

1) A syntax error would be returned
2) No rows would be selected from the EMPLOYEE table.
3) All the employee_id and name values in the EMPLOYEE table would be displayed
4) Only the rows with employee_id values equal to NULL would be included in the results.


I think the right answer is No 2, and not No 3 that the provider of this test says.
By the way the provider is SELF TEST SOFTWARE which is an authorized dealer by Oracle.

No 3 would be the right answer, if the inner query returned no rows at all, while the question says that the inner query returns NULL.


I made a test myself.

select e.employee_id, e.last_name
from employees e
where e.department_id not in ( select emp.department_id
from employees emp
where emp.employee_id = 178 )


if the inner query returns NULL, then the whole query will return no rows,
while if there is no employee with id 178 in the EMPLOYEES table ( the inner query returns no rows) the outer query will return all rows



What is your opinion?