Millal kasutada SQLis tabelite ühendamist ja millal alampäringut?

Postitas Erki Eessaar 06.04.2024 15:51 (muudeti 08.04.2024 10:00)
SQL on keeleliselt liiane, mis tähendab, et aastate jooksul on SQL keelt täiendatud aina uute keelekonstruktsioonidega ja ühte ning sama ülesannet saab seetõttu lahendada paljudel erinevatel viisidel. Mingis mõttes on see nagu inimkeeles, kus on ka palju sünonüüme ning ühte ja sama mõtet saab avaldada erinevatel viisidel. Mõni väljendusviis võib olla sobivam ühes kontekstis ja mõni teises.

Oletame, et meil on sellise kontseptuaalse struktuuriga andmebaas:

[Hotell]-1------------0..*-[Reserveerimine]

Ülesanne: Leia reserveerimised, mis on tehtud Tallinna hotellides.

Lahendus 1: 

SELECT *
FROM Reserveerimine
WHERE hotelli_nr IN (SELECT hotelli_nr
FROM Hotell
WHERE linn='Tallinn');

Lahendus 2;

SELECT Reserveerimine.*
FROM Reserveerimine INNER JOIN Hotell ON Reserveerimine.hotelli_nr=Hotell.hotelli_nr
WHERE Hotell.linn='Tallinn';

Millal kindlasti kasutada lahendust 2?
  • Päringu tulemuses on vaja väljastada andmeid tabelist Hotell. Väljastatavad andmed määratakse ära lause SELECT klauslis.
  • Päringu tulemuses on vaja väljastada andmeid mõnest muust tabelist kui Hotell, kuid selle tabelini jõudmiseks tuleb see ühendada tabeliga Hotell.
  • Päringu tulemust on vaja sorteerida andmete järgi, mis on tabelis Hotell või mõnes sellises tabelis milleni pääseb tabelit Hotell ühendades. Sorteerimiseeskiri oleks lause ORDER BY klauslis.
  • Päringu tulemusse on vaja arvutada koondandmeid andmete põhjal, mis on tabelis Hotell või mõnes sellises tabelis milleni pääseb tabelit Hotell ühendades. Koondandmete arvutamiseks vajaliku grupeerimise eeskiri oleks lause GROUP BY klauslis.

Ülesanne: Kustuta selliste reserveerimiste kommentaarid, mis on tehtud Tallinna hotellides

Lahendus 1:

UPDATEReserveerimine SET kommentaar=NULL
WHERE hotelli_nr IN (SELECT hotelli_nr
FROM Hotell
WHERE linn='Tallinn');

Miks mitte kasutada ühendamist?

Tõepoolest, see oleks võimalik, kuid erinevate andmebaasisüsteemide SQL dialektides e mägimurrakutes oleks see lause erinev. Seega kood on vähem porditav e erinevate süsteemide vahel ülekantav.

Lahendus 2 MS Accessis:

UPDATE Reserveerimine INNER JOIN Hotell ON Reserveerimine.hotelli_nr = Hotell.hotelli_nr SET Reserveerimine.kommentaar = Null
WHERE Hotell.linn='Tallinn';

Lahendus 2 PostgreSQLis

UPDATE Reserveerimine SET kommentaar=NULL
FROM Hotell
WHERE Reserveerimine.hotelli_nr=Hotell.hotelli_nr
AND Hotell.linn='Tallinn';

NB! Veel üks võimalik lahendus (nii PostgreSQLis kui MS Accessis):

UPDATE Reserveerimine AS R SET kommentaar = NULL
WHERE EXISTS (SELECT *
FROM Hotell, Reserveerimine AS R2
WHERE Hotell.hotelli_nr=R2.hotelli_nr
AND linn='Tallinn'
AND R.hotelli_nr=R2.hotelli_nr
AND R.ruumi_nr=R2.ruumi_nr
AND R.kylalise_nr=R2.kylalise_nr
AND R.alguse_aeg=R2.alguse_aeg
);

UPDATE  lause WHERE klauslis on tingimus (EXISTS (korreleeruv alampäring)), mille täidetust saab kontrollida. Alampäringu viimased neli tingimust seovad alampäringu ja põhilause ning arvestavad sellega, et reserveerimise unikaalne identifikaator on hotelli_nr, ruumi_nr, külalise_nr ja alguse_aeg kombinatsioon. Kuna nii põhilauses kui alampäringus viidatakse samale tabelile (Reserveerimine), siis selleks, et käsitleda neid lause kontekstis justkui kahte erinevat tabelit kasutatakse aliasi (R ja R2).

Ülesanne: Kustuta reserveerimised, mis on tehtud Tallinna hotellides

Lahendus 1:

DELETE FROM Reserveerimine
WHERE hotelli_nr IN (SELECT hotelli_nr
FROM Hotell
WHERE linn='Tallinn');

Miks mitte kasutada ühendamist?

Tõepoolest, see oleks võimalik, kuid erinevate andmebaasisüsteemide SQL dialektides e mägimurrakutes oleks see lause erinev. Seega kood on vähem porditav e erinevate süsteemide vahel ülekantav.

Lahendus 2 MS Accessis:

DELETE Reserveerimine.*
FROM Reserveerimine INNER JOIN Hotell ON Reserveerimine.hotelli_nr = Hotell.hotelli_nr
WHERE Hotell.linn='Tallinn';

Lahendus 2 PostgreSQLis

DELETE FROM Reserveerimine
USING Hotell
WHERE Reserveerimine.hotelli_nr=Hotell.hotelli_nr
AND Hotell.linn='Tallinn';
 
NB! Veel üks võimalik lahendus (nii PostgreSQLis kui MS Accessis):

DELETE FROM Reserveerimine AS R
WHERE EXISTS (SELECT *
FROM Hotell, Reserveerimine AS R2
WHERE Hotell.hotelli_nr=R2.hotelli_nr
AND linn='Tallinn'
AND R.hotelli_nr=R2.hotelli_nr
AND R.ruumi_nr=R2.ruumi_nr
AND R.kylalise_nr=R2.kylalise_nr
AND R.alguse_aeg=R2.alguse_aeg
);

DELETE lause WHERE klauslis on tingimus (EXISTS (korreleeruv alampäring)), mille täidetust saab kontrollida. Alampäringu viimased neli tingimust seovad alampäringu ja põhilause ning arvestavad sellega, et reserveerimise unikaalne identifikaator on hotelli_nr, ruumi_nr, külalise_nr ja alguse_aeg kombinatsioon. Kuna nii põhilauses kui alampäringus viidatakse samale tabelile (Reserveerimine), siis selleks, et käsitleda neid lause kontekstis justkui kahte erinevat tabelit kasutatakse aliasi (R ja R2).

Hinda postitust:

Keskmine hinne : Pole veel hinnanguid!