SELECT teikums
Pārējos rakstus var lasīt SQL pamatos.
Kādi ir kopas operatori un ko tie dara?
SQL standarts paredz šādus kopas operatorus UNION, EXCEPT, INTERSECT un katram no tiem divas iespējas ALL vai DISTINCT (noklusētā). Tiesa gan spriežot pēc dokumentācijas pilnu funkcionalitāti nodrošina tikai DB2 (no šeit uzskaitītajām datu bāzēm), bet, piemēram, Oracle, Microsoft SQL Server un MySQL katrs nodrošina tikai daļu standarta funkcionalitātes.
OK skaidrs, Jūs teiksiet, bet ko tad tie kopas operatori dara? Kopas operatori kombinē divu atsevišķu SQL pieprasījumu rezultātus vienā rezultātā, kur katrs no šiem SQL pieprasījumiem ir pilntiesīgs SQL Select teikums un to var izpildīt atsevišķi. To vispārīgā forma ir šāda:
P1 OPERATORS P2
kur P1 un P2 katrs ir atsevišķs pilntiesīgs SQL Select teikums.
Operators | Rezultāts | Piezīmes |
---|---|---|
P1 UNION [DISTINCT] P2 | Visi ieraksti no P1 un P2, ne vairāk kā vienu reizi | Daudzās datubāzēs distinct neļauj rakstīt, tā vietā raksta vienkārši UNION |
P1 UNION ALL P2 | Visi ieraksti no P1 un P2 neveicot nekādu unikālu ierakstu atsijāšanu | |
P1 EXCEPT [DISTINCT] P2 | Ieraksti, kas ir P1 izņemot tos ierakstus, kas ir P2 | Oracle šī operatora vietā ir operators MINUS. Daudzās datubāzēs distinct neļauj rakstīt, tā vietā raksta vienkārši EXCEPT vai MINUS |
P1 EXCEPT ALL P2 | Ieraksti, kas ir P1 izņemot tos ierakstus, kas ir P2 saglabājot šo ierakstu kardinalitāti | Realizēts tikai daļā datubāzu |
P1 INTERSECT [DISTINCT] P2 | Ieraksti, kas ir gan P1, gan P2 | Daudzās datubāzēs distinct neļauj rakstīt, tā vietā raksta vienkārši INTERSECT |
P1 INTERSECT ALL P2 | Ieraksti, kas ir gan P1, gan P2 saglabājot šo ierakstu kardinalitāti | Realizēts tikai daļā datubāzu |
Protams, atsevišķajiem SQL pieprasījumiem ir jāatbilst vairākiem nosacījumiem, lai tos varētu kombinēt:
-
Pieprasījumu atlasīto kolonu skaitam ir jābūt vienādam.
-
Atlasīto kolonu datu tipiem ir jābūt savā starpā vai nu vienādiem vai vismaz tādiem, lai datubāze spētu tos konvertēt no viena uz otru (katrai datubāzei jāskatās individuāli).
-
SQL kopas operatorus var lietot vairākus vienlaicīgi, t.i., piemēram,
P1 UNION ALL P2 UNION ALL P2 INTERSECT P3 MINUS P4. Tādā gadījumā jāskatās katras indivuduālās datu bāzes dokumentācijā, kā tādi tiek izpildīti, jo dažās (Oracle, gan ar piezīmi, ka tas nākotnē mainīsies) operatori vienkārši tiek izpildīti no kreisās uz labo pusi, citās (DB2) INTERSECT operators tiek izpildīts vispirms. -
Parasti kolonu nosaukumi tiek ņemti no pirmā SQL pieprasījuma.
-
ORDER BY klauzas katrā individuālā SQL pieprasījumā, izņemot pēdējo, vai nu nedrīkst būt vispār (Oracle) vai arī netiek ņemtas vērā (MySQL).
Dažas piezīmes par ātrdarbību un funkcionalitāti
- Atsevišķo SQL pieprasījumu rakstīšanā, protams, jāatceras iepriekšējā rakstā minētās piezīmes.
- Jāatceras ka visi UNION, EXCEPT (MINUS), INTERSECT bez ALL vienmēr atgriež tikai un vienīgi unikālās vērtības! Līdz ar to pat, ja jūsu pirmais SQL teikums atgriezīs 5 vienādas rindas un otrais nevienu, tad UNION rezultātā jūs dabūsiet mazāk rindiņas nekā pirmā SQL teikuma izpildes rezultātā, t.i., tikai vienu.
- Ja jums ir garantēti zināms, ka SQL teikumu rezultātā rodas unikāli ieraksti, tad lietojiet UNION ALL nevis UNION, lai datubāzei nebūtu jāveic liekas darbības un jāfiltrē ārā tikai unikālie ieraksti, galu galā patērējot vairāk resursus, bet iegūstot to pašu rezultātu.
- Kā jau teikts iepriekšējā vienkārša SQL teikuma apskatā, bez ORDER BY klauzas nekāda kārtība garantēta netiek, tāpēc vienmēr pēdējā SQL teikuma galā jāpieliek ORDER BY klauza, ja ir nepieciešams noteikts ierakstu sakārtojums. Nekādā ziņā nevajag domāt, ka pirmā SQL teikuma atgrieztie dati vienmēr tiks attēloti pirms otrā SQL teikuma datiem.
- Ja ir svarīgi zināt no kura SQL teikuma ieraksts ir nācis, tad katrā SQL teikumā var pievienot konstantu karodziņu (kolonu), kas norāda ieraksta izcelsmi un kuru tālāk var izmantot kārtošanā vai kādu citu prasību risināšanā.
- NULL vērtības lietojot kopas operatorus tiek uzskatītas kā vienādas.
Nākošajā tabulā redzami sākotnējie divu tabulu dati un katra kopas operatora rezultāts.
T1 | 10 | 10 | 10 | 20 | 20 | 20 | 30 | 40 | 40 | 50 | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
T2 | 10 | 10 | 30 | 30 | 30 | 30 | 40 | ||||||||||
T1 UNION [DISTINCT] T2 | 10 | 20 | 30 | 40 | 50 | ||||||||||||
T1 UNION ALL T2 | 10 | 10 | 10 | 20 | 20 | 20 | 30 | 40 | 40 | 50 | 10 | 10 | 30 | 30 | 30 | 30 | 40 |
T1 EXCEPT (MINUS) [DISTINCT] T2 | 20 | 50 | |||||||||||||||
T1 EXCEPT ALL T2 | 10 | 20 | 20 | 20 | 40 | 50 | |||||||||||
T1 INTERSECT [DISTINCT] T2 | 10 | 30 | 40 | ||||||||||||||
T1 INTERSECT ALL T2 | 10 | 10 | 30 | 40 |
Labākai saprašanai šo operatoru ideju ir iespējams grafiski attēlot izmantojot Venna diagrammas. Tiesa gan pilnībā atbilstošas tās ir tikai tādām kopām, kur katrs no elementiem ir tieši vienu reizi.
UNION operatora diagramma
EXCEPT (MINUS) operatora diagramma
INTERSECT operatora diagramma
Daži vaicājumu piemēri. Tabulu definīcija un tās dati ir šādi:
create table t1 (n number); create table t2 (n number); insert into t1 values (10); insert into t1 values (10); insert into t1 values (10); insert into t1 values (20); insert into t1 values (20); insert into t1 values (20); insert into t1 values (30); insert into t1 values (40); insert into t1 values (40); insert into t1 values (50); insert into t2 values (10); insert into t2 values (10); insert into t2 values (30); insert into t2 values (30); insert into t2 values (30); insert into t2 values (30); insert into t2 values (40); commit;
Piemērs 1. Atlasa datus no abām tabulām, tikai unikālos ierakstus.
SQL> SELECT n FROM t1 2 UNION 3 SELECT n FROM t2; N ---------- 10 20 30 40 50
Piemērs 2. Atlasa datus no abām tabulām, visus ierakstus.
SQL> SELECT n FROM t1 2 UNION ALL 3 SELECT n FROM t2; N ---------- 10 10 10 20 20 20 30 40 40 50 10 10 30 30 30 30 40
Piemērs 3. Atlasa datus no abām tabulām, visus ierakstus tos sakārtojot.
SQL> SELECT n FROM t1 2 UNION ALL 3 SELECT n FROM t2 4 ORDER BY n 5 / N ---------- 10 10 10 10 10 20 20 20 30 30 30 30 30 40 40 40 50
Piemērs 4. Atlasa tos datus kas ir pirmajā tabulā atņemot nost tos, kas ir otrajā, tikai unikālos ierakstus.
SQL> SELECT n FROM t1 2 MINUS 3 SELECT n FROM t2; N ---------- 20 50
Piemērs 5. Atlasa kopīgos datus abās tabulās tikai unikālos ierakstus.
SQL> SELECT n FROM t1 2 INTERSECT 3 SELECT n FROM t2; N ---------- 10 30 40
Piemērs 6. Atlasa datus no pirmās tabulas apvienojot tos ar tukšu kopu, tikai unikālos ierakstus.
SQL> SELECT n FROM t1 2 UNION 3 SELECT n FROM t1 WHERE 1=2; N ---------- 10 20 30 40 50
Piemērs 6. Atlasa datus no pirmās tabulas apvienojot tos ar tukšu kopu, visus ierakstus.
SQL> SELECT n FROM t1 2 UNION ALL 3 SELECT n FROM t1 WHERE 1=2; N ---------- 10 10 10 20 20 20 30 40 40 50
Oraclē var daļēji simulēt EXCEPT ALL un INTERSECT ALL lietojot MULTISET operatorus (kas strādā uz Nested tables) un tipu pārveidi (CAST) vienādi otrādi, kā tas aprakstīts manā angliskā bloga ierakstā. Tiesa gan tas prasa papildus objektus (tipus) datubāzē un no ātrdarbības viedokļa pie kaut cik lielākiem datu apjomiem izskatās, ka strādā maigi izsakoties ļoti lēni.
Ja arī Jūs atbalstat vēlmi, lai Oracle pievienotu savā SQL sintaksē arī trūkstošos kopas operatorus INTERSECT ALL un EXCEPT (MINUS) ALL, tad varat doties uz Oracle jauno ideju atbalsta vietu un pievienot savu balsi. Paldies jau iepriekš!