Question 287443 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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!

Tags:

2

Answers


  1. 0

    I am wondering why my code is incorrect.

    Say you have this data:

    yr Subject
    2023 Chemistry
    2023 Biology

    In this case, your code will still match the Biology row and return 2023. However, there is also a row for Chemistry in this year, and this violates the requirement.

    Login or Signup to reply.
  2. 0

    Your original query checks for years where the subject is not Chemistry or Physics, which means it could include years where only one of them (either Chemistry or Physics) was not awarded, but the other one was. It doesn’t ensure that neither Physics nor Chemistry was awarded in that year.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search