NOT IN keeps the rows whose value is not in the list. Students who take no course:

SELECT name FROM student
WHERE id NOT IN (SELECT id FROM takes);

The result is Theo Hall. Now a new student gets an advisor row before the advisor is chosen: i_id is NULL.

advisor:  s_id = S209, i_id = NULL

Which instructors advise nobody?

SELECT name FROM instructor
WHERE id NOT IN (SELECT i_id FROM advisor);

The result has no rows. Victor Lang advises nobody, but he is not in the result.

Why

NOT IN is a chain of <> joined with AND:

A comparison with NULL is UNKNOWN (Chapter 2). With AND, one UNKNOWN makes the result UNKNOWN or FALSE, never TRUE. WHERE keeps only TRUE rows, so every row is gone.

IN does not have this problem: it is a chain of = joined with OR, and one TRUE is enough.

The fixes

• Remove the NULLs from the list: add WHERE i_id IS NOT NULL to the subquery.
• Use a left join and keep the rows with no partner (Chapter 8).
• Use NOT EXISTS, the next lesson.