Multiple choice

Evaluate the following SELECT statement:

SELECT emp_id, name FROM emp WHERE emp_id NOT IN (SELECT emp_id FROM emp WHERE dept_id = 30 AND job = 'SALESMAN');

What would happen if the inner query returned a NULL value?

  1. No rows would be selected from the EMPLOYEE table.

  2. A syntax error would be returned.

  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.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When the inner query returns NULL, NOT IN (1, 2, NULL) evaluates to UNKNOWN for every comparison. A WHERE condition of UNKNOWN returns no rows, so the outer query selects nothing. This is a classic SQL NULL pitfall.

Keep practicing — related questions