Which of the following queries correctly returns the FIRST_NAME of every employee who is the sole direct report of their manager — that is, no other employee in the EMPLOYEES table shares the same MANAGER_ID value?
A. SELECT e.first_name
FROM employees e
WHERE e.manager_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM employees sub
WHERE sub.dept_id = e.manager_id
AND sub.emp_id <> e.emp_id
);
Uses sub.dept_id = e.manager_id as the correlation predicate, confusing department id with manager id. Dept_id values in the table are 10, 20, and 30; manager_id values are 100, 101, and 106. These domains never overlap, so the subquery always returns no rows, NOT EXISTS is always TRUE, and all six employees who have a non-NULL manager_id (Bob, Carol, Dave, Eve, Frank, Heidi) are returned.
B. SELECT e.first_name
FROM employees e
WHERE e.manager_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM employees sub
WHERE sub.manager_id = e.manager_id
AND sub.emp_id <> e.emp_id
);Correct answer
Filters to employees who have a recorded manager (IS NOT NULL guard), then checks that no sibling row shares the same manager_id. For Dave (manager_id 101) no other employee has manager_id 101, so the subquery returns no rows and NOT EXISTS is TRUE. All other employees with a non-NULL manager_id have at least one sibling sharing their manager (manager 100 has Bob, Carol, Heidi; manager 106 has Eve and Frank), so NOT EXISTS is FALSE for them. Only Dave is returned.
C. SELECT e.first_name
FROM employees e
WHERE e.manager_id IS NOT NULL
AND e.manager_id NOT IN (
SELECT sub.manager_id
FROM employees sub
WHERE sub.emp_id <> e.emp_id
);
Uses NOT IN with a correlated subquery that projects manager_id from all other employees. Because Alice and Grace have NULL in their manager_id column, the subquery's result set always contains NULL regardless of which employee is being evaluated. When any list member is NULL, NOT IN evaluates to UNKNOWN — never TRUE — for every value tested, so no employee is ever included in the result. This is the NULL-in-NOT-IN trap.
D. SELECT e.first_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees sub
WHERE sub.manager_id = e.manager_id
AND sub.emp_id <> e.emp_id
);
Omits the IS NOT NULL guard on e.manager_id. For Alice and Grace, whose manager_id is NULL, the subquery condition sub.manager_id = e.manager_id becomes NULL = NULL, which Oracle evaluates as UNKNOWN; the WHERE clause is never satisfied, the subquery returns no rows, NOT EXISTS is TRUE, and both employees are incorrectly included alongside Dave. The result contains three rows instead of one.
Explanation
NOT EXISTS is the reliable idiom for 'no peer shares this value': the correlation predicate uses ordinary equality, so any NULL manager_id values in sibling rows simply make the WHERE clause UNKNOWN and those rows are excluded from the subquery — the outer IS NOT NULL guard then prevents employees without a manager from being evaluated at all. NOT IN fails silently when the subquery's projection contains even one NULL: the expression expands to a conjunction that includes a comparison with NULL, which is always UNKNOWN under three-valued logic, so the entire NOT IN expression is UNKNOWN for every row and the query returns nothing. The self-exclusion predicate (emp_id <> emp_id of the outer row) is also essential; without it the subquery always finds the outer employee among the inner rows, NOT EXISTS is always FALSE, and no employee qualifies regardless of how many direct reports their manager has.