List Years with no Medicine Nobel in SQLZoo

user_1625098 0
Viewed 25

We are asked to find the years when the noble prize in medicine was not given. I would like to know why one query does not work while the other one does:

Attempted Solution: 

SELECT DISTINCT yr
  FROM nobel
    WHERE subject <> 'Medicine'

and why this one seems to work

Working Solution: 

SELECT DISTINCT yr
  FROM nobel
    WHERE yr NOT IN (
                     SELECT DISTINCT yr
                         FROM nobel
                         WHERE subject = 'Medicine'
                    )
select postgresql sql
2 Answers

The first query will select the years of all the Nobel prizes that aren't for medicine. So, for example, if in a certain year prizes were awarded for medicine and physics, you'll still get this year due to the physics prize. The second query selects all the years that aren't years with a prize for medicine, which is what you wanted to get.

user_24227760

Mureinik's explanation of the existing queries is clear and correct (+1).

I would like to point out that you can get the same result with a simple aggregate query and a having clause:

select yr
from nobel
group by yr
having count(*) filter(where subject = 'Medicine') = 0

This aggregates on years, and filter on those that had no Medecine subject. This query scans (and groups) the table only once.

user_106767160
Related