Otazka na SQL - NOT IN

[Honza Pazdziora]

On Wed, Feb 07, 2001 at 05:09:55PM +0100, Milan Sorm wrote:
> # 
> # SELECT id FROM k_uzivatele WHERE ___id___ NOT IN (
> #         SELECT ___uzivatel___ FROM s_studium_obecne WHERE datum_vyrazeni IS NULL
> 
> s_studium_obecne.uzivatel je FK do k_uzivatele(id)

Neni mozne, ze v k_uzivatele je take sloupec uzivatel? Pak by to
davalo smysl, ze ten puvodni select nic nevrati (nic by ale nemel
vratit ani where not exists).

> # Tohle by fungovalo jenom v pripade ze sloupce id a uzivatel obsahuji totozna data.
> # 
> # Byva lepsi pouzivat not exists misto not in. Teda pokud to jde.
> 
> cim se ty dva lisi ?

V tom, ze exists a not exists v podstate vynucuje nested loops
a pouziti indexu, zatimco not in muze vest na obskurni vypocetni
cesty, typicky na full scan tabulky.

Co vlastne rika explain o tom not in?

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, auth. WWW servers, XML/XSL, ...
Petition for a Software Patent Free Europe http://petition.eurolinux.org
------------------------------------------------------------------------