IN un EXISTS salīdzināšanas operatori

Vairums SQL izstrādātāju droši vien ir dzirdējuši par IN un EXISTS operatoriem. Tos parasti izmanto WHERE klauzāapakšvaicājumus, lai ierobežotu atgriežamo datu apjomu. Tad nu paskatīsimies sīkāk ko tie dara un kādas īpašības tiem piemīt.

IN

IN operatora sintakse ir šāda:

WHERE <izteiksme> IN <apakšvaicājums>

<izteiksme> ir tas ko mēs gribam pārbaudīt, savukārt apakšvaicājums ir pilnīgi normāls pastāvīgs SQL teikums (tas var būt arī tiešs vērtību uzskaitījums kā redzams piemēros zemāk). Parasti izteiksme ir vienkārša kolona un apakšvaicājums atgriež arī vienu kolonu. Tādā gadījumā šādu SQL vaicājumu:

WHERE kolona IN (SELECT cita_kolona FROM cita_tabula)

kur apakšvaicājums atgriež vērtības 1, 2, …, n konkrētam gadījumam var loģiski pārrakstīt kā:

WHERE kolona = 1 OR kolona = 2 OR ... OR kolona = n

Tagad ķeramies pie piemēriem. Tiem tiks izmantotas 2 tabulas (šoreiz es necentīšos piešķirt nekādu lielo jēgu, vienkārši paspēlēsimies ar skaitlīšiem):

CREATE TABLE a (
  x1 integer,
  x2 integer,
  x3 integer);
INSERT INTO a VALUES (1, 1, 1);
INSERT INTO a VALUES (2, 2, 3);
INSERT INTO a VALUES (NULL, 1, 1);
CREATE TABLE b (   y1 integer,
  y2 integer,
  y3 integer);
INSERT INTO b VALUES (1, 1, 1);
INSERT INTO b VALUES (1, 2, 1);
INSERT INTO b VALUES (2, 2, 3);
INSERT INTO b VALUES (3, 1, 1);
INSERT INTO b VALUES (NULL, 1, 1);
INSERT INTO b VALUES (NULL, 1, 1);
COMMIT;

Piemērs 1. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības ir atrodamas tabulā a .
SQL> SELECT * FROM b
  2  WHERE y1 IN (
  3    SELECT x1
  4    FROM a);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Kā redzams piemērā 1 rezultāts ir gandrīz tāds kā vairums ļaužu varētu iedomāties. Tātad atlasīti tie ieraksti, kuriem y1 vērtības ir 1 vai 2. Kāpēc 3 nav ir skaidrs, jo vērtība 3 tabulas a kolonā x1 neparādās. Bet kāpēc nav vērtības, kur y1 ir NULL? Ļoti vienkārši – atceramies NULL īpašības un jo sevišķi to, kā notiek salīdzināšana ar NULL. Tātād skatamies augstāk, kur es rakstīju kā var pārrakstīt SQL teikumu kurš ir ar IN salīdzināšanas operatoru. Šai gadījumā vaicājums būtu kā

Piemērs 2. Iepriekšējais piemērs kurš konkrētiem datiem pārveidots izmantojot loģisko VAI.
SQL> SELECT * FROM b
  2  WHERE y1 = 1 OR y1 = 2 OR y1 = NULL;
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Protams NULL = NULL (no pēdējā loģiskā VAI y1 = NULL), nekad nav Patiess, tātad netiek arī atgriezts rezultātā.

Nu un galu galā ekvivalenti mēs šo SQL vaicājumu (konkrētajiem datiem!!! bet nevis vispārīgā gadījumā) varam pārrakstīt kā:

Piemērs 3. Vērtību uzskaitījums IN klauzā.
SQL> SELECT * FROM b
  2  WHERE y1 IN (1, 2, NULL);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Augstāk minētie piemēri salīdzināšanu veic tikai vienas kolonas ietvaros. Taču tas ir iespējams arī vektoriem – kolonu pārim, trijniekam, četriniekam un tā tālāk. Skatamies piemēru:

Piemērs 4. Salīdzināšana kolonu vektoriem (šai gadījumā pārītim).
SQL> SELECT * FROM b
  2  WHERE (y1, y2) IN (
  3    SELECT x1, x2
  4    FROM a
  5  );
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

Kā redzams rezultātā esam ieguvuši vienu ierakstu mazāk nekā iepriekšējos trīs piemēros. Kāpēc? Tāpēc, ka šai reizē vienādībai bija jāizpildās uz kolonu y1, y2 pārīti reizē. SQL serverī gan šādu vektoru salīdzināšanu Jums neizdosies paveikt, jo tas diemžēl to neatbalsta, bet MySQL gan nekādu problēmu nav.

Pie tam gribu uzsvērt, ka tas nav tas pats, kas ja mēs salīdzinātu šīs kolonas atsevišķi izmantojot loģisko UN! Nākošajā piemērā tas ir uzskatāmi redzams, ka, izmantojot divus atsevišķus apakšvaicājumus, mēs iegūstam nosacīti “maigāku” ierobežojumu un potenciāli lielāku rezultāta ierakstu kopu.

Piemērs 5. Salīdzināšana divām kolonām atsevišķi. Tas nav tas pats, kas salīdzināt kolonu pārīšus – skat piemēru 4.
SQL> SELECT * FROM b
  2  WHERE y1 IN (SELECT x1 FROM a)
  3    AND y2 IN (SELECT x2 FROM a);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3
         1          2          1 

Lai situācija būtu pavisam skaidra, salīdzināsim abus gadījumus tieši uzskaitot vērtības. Tātad 4. piemēru var pārrakstīt kā:

Piemērs 6. Salīdzināšana kolonu vektoriem (šai gadījumā pārītim) ar tiešu pārīšu vērtību uzskaitījumu.
SQL> SELECT * FROM b
  2  WHERE (y1, y2) IN (
  3    (1, 1), (2, 2), (NULL, 1));
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

Piemērs 7. Salīdzināšana divām kolonām atsevišķi ar tiešu vērtību uzskaitījumu.
SQL> SELECT * FROM b
  2  WHERE y1 IN (1, 2, NULL)
  3    AND y2 IN (1, 2, 1);
        Y1         Y2         Y3
---------- ---------- ----------
         1          2          1
         1          1          1
         2          2          3 

NOT IN 

Pirmais un galvenais runājot par NOT IN – nekad nevajadzētu iedomāties, ka NOT IN ir apgrieztais operatoram IN jeb IN pārpalikums jeb, ka ar NOT IN varēs dabūt visus pārējos ierakstus, ko nedabūja ar IN. Tas tā nav vismaz tiklīdz runa ir par NULL vērtībām. NULL bija jau mazliet “aizdomīgas” IN operatoram, bet NOT IN gadījumā tas ir vēl mazāk saprotami pirmajā brīdī. Tātad ķeramies pie piemēriem. Ņemsim to pašu 1 piemēru, tikai pieliksim klāt NOT.

Piemērs 8. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības NAV atrodamas tabulā a.
SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a);
no rows selected

Opā! Kļūda vai ne? Mēs taču skaidri zinam, ka tabulā b ir ieraksts, kuram kolonā y1 ir vērtība 3, kas noteikti nav kolonā x1 tabulā a. Kur palicis šis ieraksts? Protams, ka tajā visā ir iesaistīts NULL. Pārrakstam piemēru uz tiešo vērtību uzskaitījumu:

Piemērs 9. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības NAV precīzā uzskaitījumā.
SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (1, 2, NULL);
no rows selected

Labāk nav kļuvis. Pārrakstam uz loģiskajiem VAI un ienesam iekšā noliegumu, kā mācīja matemātiskajā loģikā.

Piemērs 10. 9. piemēra transformācija ar mērķi saprast, kāpēc vaicājums neatgriež ierakstus.
SQL> SELECT * FROM b
  2  WHERE NOT (y1 = 1 OR y1 = 2 OR y1 = NULL);
no rows selected
SQL> SELECT * FROM b
  2  WHERE y1 <> 1 AND y1 <> 2 AND y1 <> NULL;

no rows selected

No pēdējā transformētā vaicājuma jau visiem vajadzētu būt skaidram, kur “suns” ir aprakts – salīdzināšanā ar NULL un tajā, ka izteiksme, kas sastāv no loģiskajiem UN var būt patiesa tad un tikai tad, ja visi tās locekļi ir patiesi. Tiklīdz kā mēs novācam nost NULL, tā uzreiz rezultāts ir tāds kā parasti cilvēki to sagaida:

Piemērs 11. Izvairoties no NULL vērtībām NOT IN operatorā iegūst vēlamo rezultātu.
SQL> SELECT * FROM b
  2  WHERE y1 <> 1 AND y1 <> 2;
        Y1         Y2         Y3
---------- ---------- ----------
         3          1          1
SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a
  5    WHERE x1 IS NOT NULL);
        Y1         Y2         Y3
---------- ---------- ----------
         3          1          1

EXISTS

EXISTS operatora sintakse ir šāda:

WHERE EXISTS <apakšvaicājums>

EXISTS predikāts dod vērtību Patiess tad, ja apakšvaicājums atgriež vismaz vienu ierakstu. Neko daudz vairāk arī tur nav ko piebilst un ķeramies pie piemēriem.

Piemērs 12. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības ir atrodamas tabulā a. Redzam, ka rezultāts ir identisks piemēram 1.
SQL> SELECT * FROM b
  2    WHERE EXISTS (
  3      SELECT 1
  4      FROM a
  5      WHERE a.x1 = b.y1)
  6  /
        Y1         Y2         Y3
---------- ---------- ----------
         1          2          1
         1          1          1
         2          2          3

Protams, ka apakšvaicājumā var salīdzināt arī vairākas kolonas reizē, tas būtu ekvivalenti ar IN operatoru salīdzinot vektorus.

Piemērs 13. Atlasa ierakstus tabulā b, kuriem kolonu pāra y1, y2 vērtības ir atrodamas tabulā a. Redzam, ka rezultāts ir identisks piemēram 4.
SQL> SELECT * FROM b
  2    WHERE EXISTS (
  3      SELECT 1
  4      FROM a
  5      WHERE a.x1 = b.y1
  6        AND a.x2 = b.y2);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

NOT EXISTS

NOT EXISTS predikāts dod vērtību Patiess tad, ja apakšvaicājums neatgriež nevienu ierakstu. Atšķirībā no NOT IN, kas vispārīgā gadījumā nedod pretēju rezultātu IN operatoram, NOT EXISTS dod pretējo rezultātu EXISTS predikātam. Otra lieta, kam ir vērts pievērst uzmanību – funkcionāli vienādiem (tādi, kas dos vienu un to pašu rezultātu) SQL teikumiem ar IN un EXISTS, pieliekot noliegumu NOT rezultāts ir atšķirīgs (skat piemēru pārīšus 12, 1 un 14, 8).

Piemērs 14. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības nav atrodamas tabulā a. Redzam, ka rezultātā iegūstam tos ierakstus no tabulas b, kuri netiak atlasīti piemērā 12.
SQL> SELECT * FROM b
  2  WHERE NOT EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
        Y1         Y2         Y3
---------- ---------- ----------
                    1          1
                    1          1
         3          1          1 

Ko lietot labāk – IN vai EXISTS?

Tātad tradicionālais jautājums. Ar tradicionālu atbildi – atkarībā no apstākļiem un iespējams, ka patiesībā nav nav nekādas nozīmes, jo Oracle klusiņām fonā abus izpilda izmantojot vienu un to pašu izpildes plānu :)

Visi zemākie spriedumi pamatā būs balstīti izmantojot Oracle DBVS, ja Jūs izmantojat kaut ko citu, tad skatieties paši, var ļoti gadīties, ka nebūt ne viss Jums būs tāpat.

Pirmkārt kā redzams nākošajā piemērā Oracle var veikt vaicājuma transformācijas un rezultātā gan IN, gan EXISTS iegūst vienu un to pašu izpildes plānu:

Piemērs 15. IN un EXISTS var tikt izpildīti ar vienu un to pašu izpildes plānu.
SQL> set autot traceonly explain
SQL> SELECT * FROM b
  2  WHERE y1 IN (
  3    SELECT x1
  4    FROM a);
Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=22)
1 0   HASH JOIN (SEMI) (Cost=7 Card=2 Bytes=22)
2 1     TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=4 Bytes=32)
3 1     TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6)

SQL> SELECT * FROM b
  2  WHERE EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=22)
1 0   HASH JOIN (SEMI) (Cost=7 Card=2 Bytes=22)
2 1     TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=4 Bytes=32)
3 1     TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6)

Protams, ka NOT IN un NOT EXISTS vispārīgā gadījumā tā nebūs, jo kā iepriekš jau noskaidrojām, tad tie dod funkcionāli atšķirīgu rezultātu.

Piemērs 16. NOT IN un NOT EXISTS vispārīgā gadījumā nevar tikt izpildīti ar vienu un to pašu izpildes plānu, jo to rezultāts ir atšķirīgs.
SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a);
no rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=4 Bytes=32)
1 0  FILTER
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=1 Bytes=3)
SQL> SELECT * FROM b
  2  WHERE NOT EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
3 rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=33)
1 0  HASH JOIN (ANTI) (Cost=7 Card=3 Bytes=33)
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6) 

Kā redzam NOT IN gadījumā tiek izmantota operācija FILTER, kas pēc būtības ir vienkāršs (un neefektīvs) cikls – priekš katra ieraksta tabulā B  skanējam visus ierakstus tabulā A. Savukārt NOT EXISTS gadījumā tiek vienreiz nolasīti visi tabulas A ieraksti, tad vienreiz visi tabulas B ieraksti un tad starp tiem veikta HASH JOIN operācija, kas apstrādā visus ierakstus vienā paņēmienā.

Kas būtu jādara, lai ar NOT IN panāktu šādu pašu efektīvu izpildes plānu? Ļoti vienkārši – jāizlabo dati, lai tabulā nebūtu NULL vērtības un JĀDARA ZINĀMS tas Oracle DBVS. Es nelabošu vērtības bet vienkārši izmantošu kolonas x2 un y2, kurām pielikšu NOT NULL ierobežojumus tā, lai Oracle būtu droša, ka NULL vērtības šajās kolonās nevar būt.

Piemērs 17. Ja Oracle zin, ka iesaitītajā kolonā NULL vērtības nevar būt, tad NOT IN arī var izmantot Hash Join Anti.
SQL> alter table a modify x2 not null;
Table altered.
SQL> alter table b modify y2 not null;
Table altered.
SQL> SELECT * FROM b
  2  WHERE y2 NOT IN (
  3    SELECT x2
  4    FROM a);
no rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=11)
1 0  HASH JOIN (ANTI) (Cost=7 Card=1 Bytes=11)
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=3 Bytes=9)

Un lūk esam situācijā, kad arī NOT IN izpildās tikpat efektīvi!

Lietas, kas jāatceras:

  • In klauza skatās tikai unikālās vērtības, tāpēc, ja IN klauzā vairākas reizes tieši iekļauj, vai arī apakšvaicājuma rezultātā iegūst vienu un to pašu vērtību, tas gala rezultātu nemaina.
  • NOT IN un NOT EXISTS nav viens un tas pats skat. piemērus 14 un 8.
  • NOT IN nav apgrieztais rezultāts IN skat. piemērus 1 un 8.
  • Skatieties izpildes plānus un mēģiniet saprast, kāpēc viens vai otrs variants ir labāks, bet tikpat labi var būt, ka abi varianti iekšēji tiek izpildīti vienādi.

Tālākā lasāmviela

About these ads

5 Responses to IN un EXISTS salīdzināšanas operatori

  1. Ivars says:

    Lielisks raksts! Lai gan man, darba specifikas dēļ, pēdējā laikā ar Oracle nesanāk darboties, vienu lietu par IN gan atceros – ja tajā norāda vērtības, nevis apakšvaicājumu, tad vismaz kādreiz Oracle bija 1000 vērtību limits.

    P.S. MSSQL 2005 tāda limita nav, nupat pārbaudīju.

    P.P.S. Šis komentārs nav domāts lai salīdzinātu DBVS, bet gan lai informētu par nelielu “gotchu”

  2. Gints Plivna says:

    Jā tāds limits ir gan arī 11g
    Taču man personīgi nekad nav nācies kaut ko tik monstrozu pat tuvumā sasniegt, jo man ir tādas aizdomas, ka to var dabūt tikai kaut kā dinamiski ģenerējot vērtības. Kas ir dinamiskais SQL. Kas nozīmē, ka šis vērtību uzskaitījums noteikti būs unikāls. Kas nozīmē, ka ja tādus ģenerē vairumā, tad Shared Pool tiek nopludināts ar SQL teikumiem, kuri nevienam citam nekad nebūs vajadzīgi, kas savukārt noved pie aplikācijas kura pie vairāk kā 1 lietotāja sāk vilkties :)
    Tāpēc šādā gadījumā var izmantot piemēram Objekta tipu nested table kā mainīgo pl/sqlā, kuru var izmantot SQL teikumos:

    SQL> CREATE TYPE table_of_ids IS TABLE OF number;
    2 /

    Type created.
    SQL> DECLARE
    2 t table_of_ids;
    3 v_sk number;
    4 BEGIN
    5 SELECT object_id
    6 BULK COLLECT INTO t
    7 FROM all_objects WHERE rownum <= 2000;
    8 SELECT count(*)
    9 INTO v_sk
    10 FROM all_objects WHERE object_id IN (
    11 SELECT column_value
    12 FROM TABLE (t));
    13 dbms_output.put_line(v_sk);
    14 END;
    15 /
    2000
    PL/SQL procedure successfully completed.

    Ātri, ērti, nekā dinamiska, nesačakarē shared pool, nu protams aizņem kaut kādu atmiņu un jāskatās, lai miljons lietotāju reizē nevāc miljons vērtības katrs savā mainīgajā, bet ja tā notiek, tad kaut kas nav labi ar aplikāciju.

    Kas attiecas uz DBVS salīdzināšanu, tad no problemo, tas ir normāli tās salīdzināt. Problēma parasti ir iekš tā, ka vairums cilvēku labi pārzin tikai vienu no tām un ja nespēj atrast tiešu atbilstību kā otrā izdarīt to pašu tādā pašā veidā, tad nemeklē citas metodes, kas funkcionāli dod to pašu rezultātu, bet ir specifiskas otrai bāzei.

  3. pindzele says:

    Gribu padalīties ar piemēru no dzīves, kura lieto ”[izteiksme] NOT IN [apakšvaicājums]“.
    Ļoti interesanti notiek, ja [izteiksme] reizēm ir NULL

  4. Gints Plivna says:

    Aha, tad notiek tieši tas, kas aprakstīts piemērā 8 un pilnai skaidrībai vajadzētu rasties no transformācijas, kas ir piemērā 10.

  5. Ivars says:

    Nu man šo limitu ir nācies sasniegt sensenos laikos, kad bija vajadzība veikt UPDATE vairākiem tūkstošiem ierakstu, kuru identifikatori bija atsūtīti Excel failā.

    Datus no Excel nelādēju tabulā, jo minētajā datubāze man nebija tiesību tādas veidot. :)

Atstāj atbildi

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: