0

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".

Mkay
  • 113
  • 11
  • Possible duplicate of [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) – bereal Nov 16 '19 at 13:06

1 Answers1

2

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 :)

Andi Pexton
  • 96
  • 5
  • Just to note the "SELECT 1 FROM" is a habit of mine; I always put just a 1 in the select when using exists, to show the results are not used/needed, also decades ago I think this used to have a small performance benefit :) – Andi Pexton Nov 16 '19 at 13:11