The IN operator in sql

In the nobel quiz, there is a question "Write the code which would show the year when neither a Physics or Chemistry award was given’.
My code is as following:

    SELECT yr FROM nobel 
  WHERE subject NOT IN ('Chemistry','Physics')

The correct code should be:

    SELECT yr FROM nobel 
  WHERE yr NOT IN (SELECT yr FROM nobel WHERE subject IN ('Physics', 'Chemistry'))

I am wondering why my code is incorrect. Why do we have to use the subquery for this question?
Thanks everyone!

>Solution :

I am wondering why my code is incorrect.

The requirement is you must show years "when neither a Physics or Chemistry award was given". They key understanding is asking for years with neither a Physics or Chemistry award is not the same as asking for rows with neither a Physics or Chemistry award.

Say you have this data:

With this data, a Chemistry award was given in 2023, which means you must eliminate ALL rows for the year 2023, including the Biology row. Your code did not do this.

Personally, I’d write this with either aggregation, NOT EXITS() or an exclusion join, rather than a NOT IN(), but the NOT IN() subquery in the example can work.

Here is the aggregation solution:

select yr
from nobel
group by yr
having sum(case when subject in ('Chemistry', 'Physics') then 1 else 0 end) = 0