CODEWITHSUNDEEP
mysql
monkeybiz7
monkeybiz7

Reputation: 5128

Can all NOT IN statements be written with NOT EXISTS and JOIN? (MySQL)

I'm learning MySQL using SQLzoo, and I'm stuck on the following problem:

The table 'nobel' looks something like this:

yr      subject     winner
1960    Chemistry   Willard F. Libby
1960    Literature  Saint-John Perse
1960    Medicine    Sir Frank Macfarlane Burnet
1960    Medicine    Peter Medawar
...

I'm trying to write a query that returns the following:

In which years was the Physics prize awarded but no Chemistry prize.

One way to write the query is to use NOT IN

SELECT DISTINCT yr FROM nobel WHERE subject = 'Physics' AND yr NOT IN (SELECT yr FROM nobel WHERE subject ='Chemistry')

How would I write it using NOT EXIST and JOIN statements?

Many thanks!

Upvotes: 1

Views: 35

Answers (2)

sgeddes
sgeddes

Reputation: 62831

Here is the OUTER JOIN equivalent, although I prefer using NOT EXISTS:

SELECT DISTINCT N.yr 
FROM nobel n 
    LEFT JOIN nobel n2 on n.yr = n2.yr and n2.subject = 'Chemistry'
WHERE n2.yr IS NULL
    AND n.subject = 'Physics'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Yes, I believe that all not in queries can be written using not exists. In fact, it is better to use not exists, because it handles NULL values better than not in.

For your example:

SELECT DISTINCT n.yr
FROM nobel n
WHERE n.subject = 'Physics' AND
      NOT EXISTS (SELECT 1 FROM nobel n2 WHERE n2.subject = 'Chemistry' and n2.yr = n.yr);

Upvotes: 4

Related Questions