Lauri's blog | Ühendpäringud

Ühendpäringud

Lisame veel mõned kirjed tabelisse, selleks et riikide ning linnade tabelitele ühendpäringuid teha, kombineerimaks andmeid mitmest tabelist.

-- Lisame olemasolevasse linnade tabelisse kirjeid,
-- mis viitavad riikide tabelile:
INSERT INTO linn(riik_kood,nimi) VALUES ('ee', 'Tallinn');
INSERT INTO linn(riik_kood,nimi) VALUES ('ee', 'Tartu');
INSERT INTO linn(riik_kood,nimi) VALUES ('fi', 'Helsinki');
INSERT INTO linn(riik_kood,nimi) VALUES ('fi', 'Vaasa');

Mitmest tabelist info kombineerimist nimetatakse JOIN operatsiooniks:

SELECT
    riik.nimi as "Riigi nimi",
    linn.nimi as "Linna nimi"
FROM linn
JOIN riik
    ON linn.riik_kood = riik.kood;

Päring annab vastuse, kus JOIN tingimusi täitvad read on ühendatud ning read millel vastet ei leitud, puuduvad:

 Riigi nimi | Linna nimi
------------+------------
 Eesti      | Tallinn
 Eesti      | Tartu
 Soome      | Helsinki
 Soome      | Vaasa
(4 rows)

Ühendpäringuid on põhimõtteliselt nelja tüüpi 1:

JOIN, INNER JOIN - Vali mõlemast tabelist ainult need read, mis rahuldavad ON tingimusi

LEFT JOIN, LEFT OUTER JOIN - Võta kõik read vasakpoolsest tabelist (linnad), need mis parempoolses tingimusi ei rahulda asendatakse NULL-iga

RIGHT JOIN, RIGHT OUTER JOIN - Analoogne eelmisega, kõik read parempoolsest tabelist (riigid) on esindatud

FULL JOIN, FULL OUTER JOIN - Mõlema tabeli kõik read on vähemalt üks kord esindatud, tingimustele mitte vastavate tabeli veergude väärtused asendatakse NULL-iga

Antud juhul mängiks rolli RIGHT JOIN, RIGHT OUTER JOIN või FULL JOIN, selleks et tulemustesse ilmuksid ka riigid, mille ühegi linna kohta kirjet pole:

SELECT
    riik.nimi IF AS "Riigi nimi",
    linn.nimi AS "Linna nimi"
FROM linn
FULL JOIN riik
    ON linn.riik_kood = riik.kood;

 Riigi nimi | Linna nimi
------------+------------
 Eesti      | Tallinn
 Eesti      | Tartu
 Soome      | Helsinki
 Soome      | Vaasa
 Leedu      |
 Läti       |
(6 rows)

Või vastupidi, selleks et leida ainult riigid, mille kohta kirjet pole:

SELECT
    riik.nimi AS "Riigi nimi"
FROM linn
FULL JOIN riik
        ON linn.riik_kood = riik.kood
WHERE
    linn.riik_kood IS NULL;

 Riigi nimi
------------
 Leedu
 Läti
(2 rows)

Kõikide analoogiliste välistavate päringute puhul on siiski tegu alati LEFT JOIN, RIGHT JOIN või FULL JOIN operatsiooniga:

Ühendpäringut saab ka sama tabeli pihta teha, näiteks kui on tarvis leida kirjete hulgast viimased:

CREATE TABLE katsetus(id serial, created timestamp, target varchar(10));

INSERT INTO katsetus(created, target) VALUES (NOW(), 'mati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'kati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'mati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'mati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'kati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'kati');
INSERT INTO katsetus(created, target) VALUES (NOW(), 'mati');

SELECT katsetus.id, katsetus.created, katsetus.target
FROM katsetus
JOIN (
    -- Loo vahetabel kus on ristatud viimane kuupäev ja identifikaator
    SELECT DISTINCT
        target,
        max(created) AS last_created
    FROM katsetus
    GROUP BY target) vahetabel
    ON
        -- Tekib topelttulemusi kui on samakuupäevaga kirjeid
        vahetabel.target = katsetus.target AND
        vahetabel.last_created = katsetus.created;

1: http://placeisimportant.wordpress.com/2013/02/01/visual-explanation-of-how-sql-joins-work/

Postgres SQL