Kopas operatori Select pieprasījumā

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

Tālākā lasāmviela

• Kopas operācijas wikipēdijā
• DB2 nodrošina pilnu sintaksi
• Except un Intersect SQL Serverī
• Union SQL Serverī
• UNION [ALL], INTERSECT, MINUS Operatori Oraclē
• MySQL ir tikai Union un Union all