SQL: Spajanje relacija

Primjer spajanja dvije relacija preko JOIN instrukcije ilustrirat ćemo na primjeru ovih dviju relacija (datoteka s definicijama):

a	b
a₁	b₁
a₂	b₁
A₁	B₁
A₂	B₂
A₃
A₄

(6 rows)

SELECT * FROM r1;

a	c
A₁	C₁
A₂	C₂
A₄
A₅	C₅

(4 rows)

SELECT * FROM r2;

Ima nekoliko mogućih verzija spajanja:

Prirodno spajanje

Prirodno spajanje smo već sreli kod relacijske algebre, i ono je upravo to. Povezujemo dvije relacije preko zajedničkih atributa, te u rezultirajućoj relacji se pojavljuju samo one n-torke (retci) gdje zajednički atributi imaju iste vrijednosti.

Zajednički atribut na primjeru gore navedenih relacija je a, i iste vrijednosti atributa su u slučaju za
a = A₁, A₂, A₄, tako da u konačnoj relaciji postoje tri n-torke:

a	b	c
A₁	B₁	C₁
A₂	B₂	C₂
A₄

(3 rows)

SELECT * FROM (r1 NATURAL JOIN r2);
SELECT * FROM r1 INNER JOIN r2 USING (a);

a atribut a pojavljuje se samo jedanput. Prirodno spajanje može se javiti i u kombinaciji s USING, pri čemu je u zagradi iza USING potrebno izlistati zajedničke atribute.

Lijevo spajanje

U lijevom spajanju u konačnom rezultatu imamo nadopunjenu lijevu relaciju s dodatnim atributima iz desne relacije. Vrijednosti tih dodatnih atributa ili su jednake vrijednostima koje imaju u desnoj relacji ili su jednaki NULL (prazni), što ovisi da li takva n-torka postoji u desnoj relaciji.

a	b	c
a₁	b₁
a₂	b₁
A₁	B₁	C₁
A₂	B₂	C₂
A₃
A₄

(6 rows)

SELECT * FROM (r1 NATURAL LEFT JOIN r2);
SELECT * FROM r1 LEFT OUTER JOIN r2 USING (a);

Slično kao u prirodnom spajanju moguće je koristiti USING radi točnog definiranja zajedničkih atributa.

Desno spajanje

Desno spajanje je potpuno analogno lijevom spajanju. Desnoj relaciji se pridruzuju dodatni atributi iz lijeve relacije, s vrijednostima koje imaju u lijevoj relaciju ako zajednički atribut ima istu vrijednost, ondosno NULL (prazno) ako ne postoji ista vrijednost za zajednički atribut.

a	b	c
A₁	B₁	C₁
A₂	B₂	C₂
A₄
A₅		C₅

(4 rows)

SELECT * FROM (r1 NATURAL RIGHT JOIN r2);
SELECT * FROM r1 RIGHT OUTER JOIN r2 USING (a);

Potpuno spajanje

Potpuno spajanje stvara relaciju u kojoj se pojavljuju sve n-torke iz lijeve i desne relacije, bez obzira na to da li su vrijednosti zajedničkog atributa iste. Potpuno spajanje možemo smatrati skupovnom unijom lijevog i desnog spajanja.

a	b	c
a₁	b₁
a₂	b₁
A₁	B₁	C₁
A₂	B₂	C₂
A₃
A₄
A₅		C₅

(7 rows)

SELECT * FROM (r1 NATURAL FULL OUTER JOIN r2);
SELECT * FROM r1 FULL OUTER JOIN r2 USING (a);

Pogledajmo još neke verzije spajanja:

Kartezijev (unakrsni) produkt

Dakle najjednostavnije spajanje je Kartezijev produkt gdje se kombinira svaka n-torke iz jedne relacije sa svakom n-torkom iz druge relacije. U našem slučaju to je 6x4=24 zajedničke n-torke.

a	b	a	c
a₁	b₁	A₁	C₁
a₁	b₁	A₂	C₂
a₁	b₁	A₄
a₁	b₁	A₅	C₅
a₂	b₁	A₁	C₁
a₂	b₁	A₂	C₂
...	...	...	...
...	...	...	...
A₄		A₁	C₁
A₄		A₂	C₂
A₄		A₄
A₄		A₅	C₅

(24 rows)

SELECT * FROM r1,r2;
SELECT * FROM r1 CROSS JOIN r2;

Prirodno, lijevo, desno i potpuno spajanje

Koristeći konstrukciju r₁ ... JOIN r₂ ON (..) moguće se ksplicitno specificirati listu atributa po kojima se radi spajanje relacija, čak i kada atributi nemaju isto ime. Evo niza primjera:

a	b	a	c
A₁	B₁	A₁	C₁
A₂	B₂	A₂	C₂
A₄		A₄

(3 rows)

SELECT * FROM (r1 INNER JOIN r2 ON (r1.a=r2.a));

a	b	a	c
a₁	b₁
a₂	b₁
A₁	B₁	A₁	C₁
A₂	B₂	A₂	C₂
A₃
A₄		A₄

(6 rows)

SELECT * FROM r1 LEFT OUTER JOIN r2 ON (r1.a=r2.a);

a	b	a	c
A₁	B₁	A₁	C₁
A₂	B₂	A₂	C₂
A₄		A₄
		A₅	C₅

(4 rows)

SELECT * FROM r1 RIGHT OUTER JOIN r2 ON (r1.a=r2.a);

a	b	a	c
a₁	b₁
a₂	b₁
A₁	B₁	A₁	C₁
A₂	B₂	A₂	C₂
A₃
A₄		A₄
		A₅	C₅

(7 rows)

SELECT * FROM r1 FULL OUTER JOIN r2 ON (r1.a=r2.a);

Problem: Naći ispis ocjena za studenta s indeksom F-2023!

SELECT ime,prezime FROM student WHERE indeks='F-2023';


   ime    | prezime
----------+---------
 KREŠIMIR | VURNEK
(1 row)


SELECT naslov,ocjena
    FROM ((predavanje NATURAL JOIN ispit) NATURAL JOIN kolegij)
    WHERE indeks='F-2023';


                      naslov                      | ocjena
--------------------------------------------------+--------
 Kultura govorenja i pisanja I                    |      5
 Kultura govorenja i pisanja II                   |      3
 Engleski jezik I                                 |      3
 Tjelesna i zdravstvena kultura I                 |      5
 Matematika I                                     |      3
 Matematika II                                    |      5
 Osnove fizike 1                                  |      5
 Seminar iz osnova fizike 1                       |      4
 Uvod u računarstvo                               |      3
 Obrada teksta i proračunske tablice              |      3
 Osnove fizike 2                                  |      5
 Seminar iz osnova fizike 2                       |      5
 Osnove programiranja (Pascal)                    |      5
 Uporaba kompjutorskih mreža (Internet)           |      4
 Vjerojatnost i statistika                        |      3
 Računala i operativni sustavi                    |      5
 Opća pedagogija                                  |      5
 Tjelesna i zdravstvena kultura II                |      3
 Građa računala                                   |      5
 Matematika III                                   |      3
 Matematika IV                                    |      4
 Strukture podataka i algoritmi                   |      4
 Računalni praktikum I                            |      3
 Statistička analiza i multimedijske prezentacije |      4
 Osnove fizike 3                                  |      4
 Osnove fizike 4                                  |      3
 Praktikum iz osnova fizike 1                     |      5
 Praktikum iz osnova fizike 2                     |      4
 Klasična mehanika u nastavi I                    |      4
 Klasična mehanika u nastavi II                   |      5
(30 rows)

1. Primjer korištenja prirodnog spajanja triju relacija

Normalizacija podataka zahtjeva da se raznorodni podaci čuvaju u različitim relacija. Kao rezultat toga, relacije koji sadrže o podatke ocjenama ne nalaze se u istoj relaciji koja sadrži podatke o kolegijima i njihovim naslovima. Prirodno spajanje, međitim, i ostala spajanja omogućuju stvaranje virtuelnih relacija, koje doduše ne zadovoljavaju striktne uvjete normalizacija, ali zato sadrže sve podatke koje želimo dobiti u nekom upitu. U ovom primjeru s spojili tri relacije, jednu u kojoj su ocjene, drugu koja sadrži podatke o upisanim kolegijima, te treću u kojoj su naslovi upisani kolegija, tj. položenih ispita.

Problem: Koje kolegije student VURNEK sluša ili ih još nije položio?


SELECT naslov
    FROM (
       (predavanje  NATURAL JOIN kolegij)
          INNER JOIN
        student USING(indeks))
    WHERE student.prezime='VURNEK' AND iid IS NULL;


                      naslov
---------------------------------------------------
 Psihologija odgoja i obrazovanja
 Didaktika
 Baze podataka
 Operacijski sustavi
 Računalni praktikum II
 Diferencijalne jednadžbe (dinamički sustavi)
 Elektrodinamika
 Uporaba numeričkih metoda i praktikum - (fortran)
 Uporaba kompjutora u nastavi
 Uvod u statističku fiziku
 Seminar iz uvoda u statističku fiziku
 Programiranje slučajnih brojeva
 Seminar iz medicinske fizike
(13 rows)

2. Primjer za IS NULL i USING

Relacija student i kolegij imaju zajedničke atribute prezime i ime, ali u jednoj se ime i prezime odnose na studente, a u drugoj se odnose na profesore. Kod spajanja relacija svakako ih ne želimo spojiti preko tih atributa jer im to ipak nisu zajednički atributi, već atributi koji slučajno imaju ista imena. Pa smo spajanje uradili eksplicitno navodeći listu atributa (tj. samo indeks) preko koje se relacije spajaju koristeći izraz USING. Nadalje, upotrebili smo IS NULL koji pronalazi one n-torke koje nemaju definiran atribut. Postoji varijacija koja prinalazi one n-torke koje imaju definirani atribut: IS NOT NULL.

Problem: Koje je ispite student 'VURNEK' položio u 9 mjesecu 2002 ?


SELECT naslov,ocjena
    FROM (ispit NATURAL JOIN predavanje NATURAL JOIN kolegij)
       INNER JOIN
          student USING (indeks)
    WHERE datum BETWEEN '2002-09-01' AND '2002-09-30'
    AND student.prezime='VURNEK';

                      naslov                      | ocjena
--------------------------------------------------+--------
 Opća pedagogija                                  |      5
 Tjelesna i zdravstvena kultura II                |      3
 Građa računala                                   |      5
 Matematika III                                   |      3
 Matematika IV                                    |      4
 Strukture podataka i algoritmi                   |      4
 Računalni praktikum I                            |      3
 Statistička analiza i multimedijske prezentacije |      4
 Osnove fizike 3                                  |      4
 Osnove fizike 4                                  |      3
 Praktikum iz osnova fizike 1                     |      5
 Praktikum iz osnova fizike 2                     |      4
 Klasična mehanika u nastavi I                    |      4
 Klasična mehanika u nastavi II                   |      5
(14 rows)

3. Primjer za BETWEEN

U ovom smo primjeru rabili izraz 'a BETWEEN b AND c' koji u svari znači (a >= b AND a<= c), pri čemu se podrazumjeva da je c>=b.

Kada već imamo listu ocjena zašto ne izračunati srednju ocjenu ili prosjek?

Problem: Naći prosjek ocjena studenta s indeksom F--2023!


SELECT AVG(ocjena),COUNT(*)
    FROM (predavanje NATURAL JOIN ispit) WHERE indeks='F-2023';


     avg      | count
--------------+-------
 4.0666666667 |    30
(1 row)

4. Primjer kako naći prosjek ocjena

Možemo li tako jednostavno naći prosjek ocjena i za ostale studente?

Problem: Naći poredati studente treće godine po srednjim ocjenama


SELECT ime,prezime,AVG(ocjena)
    FROM ((predavanje NATURAL JOIN ispit) NATURAL JOIN student)
    WHERE godina=3
    GROUP BY ime,prezime
    ORDER BY avg DESC;


   ime    |   prezime    |     avg
----------+--------------+--------------
 GORAN    | MATONIČKIN   | 4.2666666667
 KREŠIMIR | VURNEK       | 4.0666666667
 ANA      | JURIĆ        | 4.0000000000
 IVA      | JAREC        | 4.0000000000
 VEDRAN   | KRALJ        | 4.0000000000
 AMIR     | EL-OCH       | 3.9666666667
 MARIO    | KLOKOČKI     | 3.9333333333
 ANA      | PARTALO      | 3.7333333333
 LINO     | SCHILDENFELD | 3.6000000000
 MARIO    | JOVIČIĆ      | 3.2666666667
 DALIBOR  | NOVAK        | 3.1666666667
 MARIN    | KOSOVIĆ      | 3.0666666667
 NENAD    | KARLOVČEC    | 3.0666666667
 IVAN     | GLADOVIĆ     | 3.0000000000
 KRISTINA | RUŽOJČIĆ     | 3.0000000000
 ZRINKA   | ŠUMANOVAC    | 2.8666666667
 IVAN     | LESIĆ        | 2.8333333333
(17 rows)

ili

SELECT ime,prezime,AVG(ocjena)
    FROM ((predavanje NATURAL JOIN ispit) NATURAL JOIN student)
    WHERE godina=3
    GROUP BY ime,prezime HAVING (AVG(ocjena)>=4)
    ORDER BY avg DESC;


   ime    |  prezime   |     avg
----------+------------+--------------
 GORAN    | MATONIČKIN | 4.2666666667
 KREŠIMIR | VURNEK     | 4.0666666667
 ANA      | JURIĆ      | 4.0000000000
 IVA      | JAREC      | 4.0000000000
 VEDRAN   | KRALJ      | 4.0000000000
(5 rows)

5. Primjer kako naći najbolje studente neke godine

U ovom primjeru imamo dva nova izraza. GROUP BY se upotrebljava zajedno s agregatnim funkcijama, s tim da funkcija agregacije se primjenjuje samo na svaku pojedinu grupu n-torki, a ne cijelu relaciju. Osim toga moguće je dodatno reducirati ispis ispis rezultata koristeći kriterij naveden u HAVING izrazu. HAVING je vrsta WHERE kriterija, ali koji se primjenjuje na grupu n-torki.

Problem: Koliko studenata ima na kojoj godini?


SELECT godina,COUNT(*) AS "broj studenata" FROM student GROUP BY godina;


 godina | broj studenata
--------+----------------
      1 |             69
      2 |             31
      3 |             17
(3 rows)

6. Primjer aliasa za atribut ili funkciju agregacije

U ovim smo primjeru uveli alias (drugo ime) za rezltat COUNT funkcije, dobivajući tako smisleniji ispis rezltata.

Problem: Koja je generacija studenata najbolja?


SELECT godina,AVG(ocjena) as "prosjek godine"
    FROM (predavanje NATURAL JOIN ispit NATURAL JOIN student)
    GROUP BY godina;


 godina | prosjek godine
--------+----------------
      2 |   3.5665322581
      3 |   3.5196078431
(2 rows)

7. Primjer

U ovom fiktivnom primjeru čini se da je druga godina bolja, iako je moguće da su ispiti na nižim godinama lakši? U našem primjeru to je čista slučajnost jer su ocjene generirane slučajno!

Problem: Koja je raspodjela ocjena?


SELECT ocjena,COUNT(*) AS "raspodjela ocjena"
    FROM ispit
    GROUP BY ocjena;


 ocjena | raspodjela ocjena
--------+-------------------
      2 |               149
      3 |               340
      4 |               339
      5 |               178
(4 rows)

8. Primjer

Problem: Koja je srednja ocjena po pojedinom kolegiji?


SELECT naslov,AVG(ocjena) AS "srednja ocjena",COUNT(*) AS "broj ispita"
    FROM (predavanje NATURAL JOIN ispit NATURAL JOIN kolegij)
    GROUP BY naslov
    ORDER BY "broj ispita","srednja ocjena";


                      naslov                      | srednja ocjena | broj ispita
--------------------------------------------------+----------------+------------
 Klasična mehanika u nastavi I                    |   2.8823529412 |          17
 Matematika III                                   |   3.0588235294 |          17
 Praktikum iz osnova fizike 2                     |   3.4117647059 |          17
 Računalni praktikum I                            |   3.4117647059 |          17
 Građa računala                                   |   3.4705882353 |          17
 Osnove fizike 4                                  |   3.4705882353 |          17
 Matematika IV                                    |   3.5294117647 |          17
 Osnove fizike 3                                  |   3.5294117647 |          17
 Strukture podataka i algoritmi                   |   3.5294117647 |          17
 Praktikum iz osnova fizike 1                     |   3.6470588235 |          17
 Statistička analiza i multimedijske prezentacije |   3.6470588235 |          17
 Klasična mehanika u nastavi II                   |   3.7058823529 |          17
 Tjelesna i zdravstvena kultura II                |   3.7058823529 |          17
 Opća pedagogija                                  |   3.7647058824 |          17
 Matematika I                                     |   3.3333333333 |          48
 Uporaba kompjutorskih mreža (Internet)           |   3.3750000000 |          48
 Tjelesna i zdravstvena kultura I                 |   3.3958333333 |          48
 Osnove programiranja (Pascal)                    |   3.4166666667 |          48
 Kultura govorenja i pisanja II                   |   3.4791666667 |          48
 Osnove fizike 2                                  |   3.5208333333 |          48
 Računala i operativni sustavi                    |   3.5625000000 |          48
 Seminar iz osnova fizike 1                       |   3.5625000000 |          48
 Kultura govorenja i pisanja I                    |   3.5833333333 |          48
 Osnove fizike 1                                  |   3.5833333333 |          48
 Uvod u računarstvo                               |   3.6250000000 |          48
 Seminar iz osnova fizike 2                       |   3.6458333333 |          48
 Engleski jezik I                                 |   3.6666666667 |          48
 Matematika II                                    |   3.7291666667 |          48
 Obrada teksta i proračunske tablice              |   3.7500000000 |          48
 Vjerojatnost i statistika                        |   3.7500000000 |          48
(30 rows)

9. Primjer

Problem: Koliko je studenata upisalo koji kolegij?


SELECT naslov,COUNT(*) AS "broj studenata"
    FROM (predavanje NATURAL JOIN kolegij)
    GROUP BY naslov
    ORDER BY "broj studenata";


                      naslov                       | broj studenata
---------------------------------------------------+----------------
 Filozofija fizike                                 |              1
 Odabrana poglavlja optike                         |              1
 Povijest fizike                                   |              1
 Povijest informatike                              |              1
 Programiranje slučajnih brojeva                   |              1
 Seminar iz energetike                             |              1
 Seminar iz uvoda u astronomiju i astrofiziku      |              1
 Uvod u astronomiju i astrofiziku                  |              1
 Eksperimentalne metode moderne fizike             |              2
 Energetika                                        |              2
 Medicinska fizika                                 |              2
 Objektno orijentirano programiranje               |              2
 Odabrana poglavlja opće fizike                    |              2
 Osnove biofizike                                  |              2
 Seminar iz medicinske fizike                      |              2
 Simbolički jezici (Mathematica)                   |              2
 Seminar iz odabranih poglavlja optike             |              3
 Seminar iz osnova biofizike                       |              3
 Uporaba računala u lingvistici                    |              4
 Baze podataka                                     |             17
 Didaktika                                         |             17
 Diferencijalne jednadžbe (dinamički sustavi)      |             17
 Elektrodinamika                                   |             17
 Operacijski sustavi                               |             17
 Psihologija odgoja i obrazovanja                  |             17
 Računalni praktikum II                            |             17
 Seminar iz uvoda u statističku fiziku             |             17
 Uporaba kompjutora u nastavi                      |             17
 Uporaba numeričkih metoda i praktikum - (fortran) |             17
 Uvod u statističku fiziku                         |             17
 Građa računala                                    |             48
 Klasična mehanika u nastavi I                     |             48
 Klasična mehanika u nastavi II                    |             48
 Matematika III                                    |             48
 Matematika IV                                     |             48
 Opća pedagogija                                   |             48
 Osnove fizike 3                                   |             48
 Osnove fizike 4                                   |             48
 Praktikum iz osnova fizike 1                      |             48
 Praktikum iz osnova fizike 2                      |             48
 Računalni praktikum I                             |             48
 Statistička analiza i multimedijske prezentacije  |             48
 Strukture podataka i algoritmi                    |             48
 Tjelesna i zdravstvena kultura II                 |             48
 Engleski jezik I                                  |            117
 Kultura govorenja i pisanja I                     |            117
 Kultura govorenja i pisanja II                    |            117
 Matematika I                                      |            117
 Matematika II                                     |            117
 Obrada teksta i proračunske tablice               |            117
 Osnove fizike 1                                   |            117
 Osnove fizike 2                                   |            117
 Osnove programiranja (Pascal)                     |            117
 Računala i operativni sustavi                     |            117
 Seminar iz osnova fizike 1                        |            117
 Seminar iz osnova fizike 2                        |            117
 Tjelesna i zdravstvena kultura I                  |            117
 Uporaba kompjutorskih mreža (Internet)            |            117
 Uvod u računarstvo                                |            117
 Vjerojatnost i statistika                         |            117
(60 rows)

10. Primjer

Još još jedan čudni statistički primjer.

Problem: Koliko ima dugih ili kratkih prezimena među studentima


SELECT COUNT(*) AS "broj studenata",LENGTH(prezime) AS "dužina prezimena"
    FROM student
    GROUP BY "dužina prezimena";


 broj studenata | dužina prezimena
----------------+------------------
              1 |                3
              4 |                4
             24 |                5
             21 |                6
             27 |                7
             20 |                8
             13 |                9
              4 |               10
              2 |               11
              1 |               12
(10 rows)

11. Primjer za funkciju LENGTH

Moguće je pojedinim SELECT SQL upitima dati posebno ime, npr. onima koje često koristimo, preko izraza CREATE VIEW. Pogledajmo primjer

Problem: Tražimo listu imena i indeksa studenata 3. godine studija 'profesor fizike i informatike'

CREATE VIEW pfi_3
        AS SELECT ime,prezime,indeks
	      FROM student WHERE smjer='pfi' AND godina=3;
	      ORDER BY prezime,ime;

SELECT * FROM pfi_3;

   ime    |   prezime    | indeks
----------+--------------+--------
 AMIR     | EL-OCH       | F-2025
 IVAN     | GLADOVIĆ     | F-1823
 IVA      | JAREC        | F-2291
 MARIO    | JOVIČIĆ      | F-2288
 ANA      | JURIĆ        | F-1937
 NENAD    | KARLOVČEC    | F-1872
 MARIO    | KLOKOČKI     | F-1851
 MARIN    | KOSOVIĆ      | F-1830
 VEDRAN   | KRALJ        | F-1972
 IVAN     | LESIĆ        | F-1883
 GORAN    | MATONIČKIN   | F-2019
 DALIBOR  | NOVAK        | F-2284
 ANA      | PARTALO      | F-1925
 KRISTINA | RUŽOJČIĆ     | F-2007
 LINO     | SCHILDENFELD | F-1863
 KREŠIMIR | VURNEK       | F-2023
 ZRINKA   | ŠUMANOVAC    | F-1789
(17 rows)

12. Primjer za definiranje pogleda

Pogled ili VIEW se ponaša kao posebna relacija. U tu relaciju nije moguće upisivati podatke ili ih mijenjati. Pogled se može poništiti ili izbrisati naredbom DROP VIEW <ime_pogleda>.

Spajanje relacija

1. Spajanja relacija - jednostavni primjer

2. Primjeri iz naše baze podataka o fakultetu