CODEWITHSUNDEEP
postgresql
Mkay
Mkay

Reputation: 123

What's the difference between "NOT EXISTS" and "NOT IN"

For school I needed to get all players who never played a match for the team with number 1. So I thought I would look for all players who played a match for the team with number 1 in a subquery. This is my subquery: 

select distinct s.spelersnr, naam 
from spelers s inner join wedstrijden w on (s.spelersnr = w.spelersnr and teamnr = 1)

Now to extract the players who never played a match for team 1 I thought I could use the "NOT EXISTS" operator. My query then looked like this: 

select spelersnr, naam
from spelers
where not exists (select distinct s.spelersnr, naam
from spelers s inner join wedstrijden w on (s.spelersnr = w.spelersnr and teamnr = 1))
order by naam, spelersnr

But this query didn't return the result I needed (in fact it didn't return anything). Then I tried this query: 

select spelersnr, naam
from spelers
where (spelersnr, naam) not in (select distinct s.spelersnr, naam
from spelers s inner join wedstrijden w on (s.spelersnr = w.spelersnr and teamnr = 1))
order by naam, spelersnr

This query returned the result I needed, but now I don't really understand the difference between "NOT EXISTS" and "NOT IN".

Upvotes: 0

Views: 85

Answers (1)

Andi Pexton
Andi Pexton

Reputation: 96

You where close, the difference between the NOT EXISTS and NOT IN is how the SQL is executed, using NOT IN will be much slower (obviously this depends on the size of the data sets involved) because it compares each row to each of the items in the clause.

in contrast EXISTS or NOT EXISTS does a lookup for the single row based on a a contextual where clause.

All that's missing in you example is the WHERE clause in the EXISTS 

select spelersnr, naam
from spelers as sp
where not exists (select 1
from spelers s inner join wedstrijden w on (s.spelersnr = w.spelersnr and teamnr = 1)
where sp.spelersnr = s.spelersnr and sp.naam = s.naam)
order by naam, spelersnr

Hope this helps :)

Upvotes: 2

Related Questions