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

    SQL union operator

    EXCEPT (MINUS) operatora diagramma

    SQL except operator

    INTERSECT operatora diagramma

    SQL intersect operator

    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

    2 Responses to Kopas operatori Select pieprasījumā

    1. Gints Plivna says:

      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.

    2. Gints Plivna says:

      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š!

    Komentēt

    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

    %d bloggers like this: