Vairums SQL izstrādātāju droši vien ir dzirdējuši par IN un EXISTS operatoriem. Tos parasti izmanto WHERE klauzā kā 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
Ierakstīja Gints Plivna 
Datubāzes 