Mi a különbség az IN és az EXISTS között?

Látszólag semmi: A

Select * from A
where Col1 in (Select Col1 from B)

Select * from A
where Exists (Select Col1 from B where Col1=A.Col1)

lekérdezések mindig azonos eredményt adnak, (azaz az IN és az EXISTS felcserélhető) de ha negáljuk őket azaz:

Select * from A
where Col1 not in (Select Col1 from B)

Select * from A
where not Exists (Select Col1 from B where Col1=A.Col1)

és a B tábla Col1 oszlopa tartalmaz NULL értéket, akkor a két lekérdezés eltérő eredményt ad. Az Exists-es változat azt, amit várunk, de az IN-es változat nem ad vissza egyetlenegy sort sem.

Ahhoz, hogy megértsük ennek okát, először azt kell megvizsgálnunk, hogy kezeli az SQL szerver a NULL értékeket. SQL-ben a NULL jelentése ismeretlen. Ismeretlent hasonlítva valamihez szintén ismertetlent kapunk. Azaz a:

Select * from Tábla where oszlop = NULL
Select * from Tábla where oszlop <> NULL

lekérdezések ugyanazt az eredményt adják: nevezetesen semmit. (Ismeretlent összehasonlítva egy konkrét értékkel szintén ismeretlent (NULL) kapunk). Ezt általában nem is szoktuk elrontani, hiszen NULL értékek keresésére az IS NULL vagy az IS NOT null módszereket használjuk.

Most nézzük meg hogyan működik az IN (Először a nem negált lekérdezés esetén)

Select * from A
where Col1 IN (NULL, 1, 2)

Ezt a lekérdezést átírhatjuk a következő ekvivalens formátumra:

Select * from A
where Col1 = NULL or Col1 = 1 or Col1 = 2

és a lekérdezés azokat a rekordokat adja vissza A táblából amelyek vagy 1-gyel, vagy 2-vel egyenlőek. Most vizsgáljuk meg ennek negáltját

Select * from A
where Col1 NOT IN (NULL, 1, 2)

Ezt a lekérdezést is átírhatjuk a következő ekvivalens formátumra: (itt felhasználtuk a de Morgan azonosságok közül, hogy NEM (A vagy B) = NEM A és NEM B

Select * from
A where Col1 <> NULL And Col1 <> 1 and Col1 <> 2

Ahhoz, hogy a fenti lekérdezés eredményt adjon vissza a Col1 <> NULL és a Col1 <> 1, ... feltételeknek mind igaznak kell lenniük. Namost a Col1 <> NULL nem lehet igaz (láttuk fent), így az egész feltétel hamis, tehát nem lehet egyetlen olyan rekord sem az A táblában ami ezeknek a feltételeknek megfelel. Függetlenül attól, hogy A táblának létezik e olyan rekordja ami egyenlő 1-gyel, vagy nem.

Szumma szummárum, használjuk a NOT IN-t akkor ha listában fel tudjuk sorolni az elemeket és használjuk a NOT EXISTS-et ha táblák oszlopaiban keresünk.