Andmebaasid I (ITI0206) (kevad 2024)

Vastus (25.10.2023 20:01): Vaatlen näitena andmebaasi, millel on kontseptuaalne struktuur: [Dept]-0..1-----------0..*-[Emp]
SIIN on PostgreSQL andmebaasi loomise laused ja järgnevat koodi saab katsetada näiteks DB Fiddle keskkonnas.

INNER JOIN

Need kaks lauset:

SELECT *
FROM Dept INNER JOIN Emp ON Dept.deptno=Emp.deptno
AND Dept.dname LIKE 'A%';

SELECT *
FROM Dept INNER JOIN Emp ON Dept.deptno=Emp.deptno
WHERE Dept.dname LIKE 'A%';

ja need kaks lauset:

SELECT *
FROM Dept INNER JOIN Emp ON Dept.deptno=Emp.deptno
AND Emp.sal>1000;

SELECT *
FROM Dept INNER JOIN Emp ON Dept.deptno=Emp.deptno
WHERE Emp.sal>1000;

annavad samasuguse tulemuse.

Lisakommentaarid:

• Mõni andmebaasisüsteem (nt MS Access) ei toeta lisapiirangute kirjutamist FROM klausli ühendamise tingimusse. PostgreSQL ja Oracle näiteks toetavad. Seega väheneb koodi porditavus.
• PostgreSQL ja Oracle näitel on lausete täitmisplaanid ja seega ka täitmise kiirus ühesugused.
• Pannes lisatingimused FROM klauslisse ei saa kasutada tabelite ühendamisel mugavat ja kompaktset USING (<ühised veerud>) süntaksi.
  • Selline lause ei toimi: SELECT * FROM Dept INNER JOIN Emp USING (deptno) AND Emp.sal>1000;
• Vanema ühendamise süntaksi puhul toodi probleemina välja, et WHERE klausel on tingimustega ülekoormatud. Pannes kõik tingimused FROM klauslisse on tulemus samasugune.
• Olukorras, kus laused annavad ühesuguse tulemuse, ei ole kõigi tingimuste koondamine FROM klauslisse mõttekas.

Veel üks näide - tabelite põhjal, kus on palju rohkem andmeid (tabelis Health_care_visit on 1_000_000 rida). Täitmisplaanid ja täitmise kiirus on ühesugune, kuid loetavuse poolest on minu arvates FROM ja WHERE klausli eristamine parem.

SELECT Hcv.*
FROM Health_care_visit AS Hcv INNER JOIN Facility AS F ON Hcv.facility_id=F.facility_id
AND EXTRACT(year FROM Hcv.from_date) >2010 AND F.facility_name ILIKE 'A%'
INNER JOIN Postal_address AS P ON Hcv.postal_address_id=P.postal_address_id AND P.city='Kaduna';

SELECT Hcv.*
FROM Health_care_visit AS Hcv INNER JOIN Facility AS F USING (facility_id)
INNER JOIN Postal_address AS P USING (postal_address_id)
WHERE EXTRACT(year FROM Hcv.from_date) >2010
AND F.facility_name ILIKE 'A%'
AND P.city='Kaduna';

OUTER JOIN

Laused annavad erineva tulemuse.

SELECT *
FROM Dept LEFT JOIN Emp ON Dept.deptno=Emp.deptno
AND Dept.dname LIKE 'A%';

Tulemuses on kõik osakonnad (töötajatega või mitte) - ka sellised, mille nimi ei alga A tähega.

SELECT *
FROM Dept LEFT JOIN Emp ON Dept.deptno=Emp.deptno
WHERE Dept.dname LIKE 'A%';

Tulemuses on ainult sellised osakonnad (töötajatega või mitte), mille nimi algab A tähega.

SELECT *
FROM Dept LEFT JOIN Emp ON Dept.deptno=Emp.deptno
AND Emp.sal>1000;

Tulemuses on töötajad, kelle palk on üle 1000 EUR + üks rida iga osakonna (Dept) kohta, milles pole ühtegi üle 1000 EUR palgaga töötajat.
Seda saab kasutada, et lahendada ülesanne: Leia osakonnad, kus pole ühtegi üle 1000 EUR palgaga töötajat.

SELECT Dept.*
FROM Dept LEFT JOIN Emp ON Dept.deptno=Emp.deptno
AND Emp.sal>1000
WHERE Emp.deptno IS NULL;

Järgneva päringu tulemuses on töötajad, kelle palk on üle 1000 EUR.

SELECT *
FROM Dept LEFT JOIN Emp ON Dept.deptno=Emp.deptno
WHERE Emp.sal>1000;