Vaatame näidet.

Kontseptuaalne mudel
  • [Facility]-1---------0..*-[Health_care_visit]
    • Facility – Tervishoiuasutus 
    • Health_care_visit – Visiit arsti juurde
Andmebaas
  • Realiseeritud PostgreSQL 18
  • Tabel Facility – 50 000 rida
  • Tabel Health_care_visit – 1 000 000 rida

Ülesanne: Leia kõik arstivisiidid, mis on tehtud tervishoiuasutusse nimega "Zoonder1267". Võimalikud lahendused.

Lahendus 1
SELECT hcv.*
FROM Health_care_visit AS hcv INNER JOIN Facility USING (facility_id)
WHERE facility_name='Zoonder1267';

FROM klauslis on ühendamise tingimus ja WHERE klauslis on täiendav piirav tingimus. 

Lahendus 2
SELECT hcv.*
FROM Health_care_visit AS hcv INNER JOIN Facility ON hcv.facility_id=Facility.facility_id AND facility_name='Zoonder1267';

FROM klauslis on nii ühendamise tingimus kui ka täiendav piirav tingimus. 

Analüüs
  • Laused annavad sama tulemuse.
  • Sisemiselt on mõlemal lausel sama täitmise plaan ja seega täitmise kiirused ei erine. Seega lahendus 2 ei anna jõudluse mõttes eelist.
  • Lahenduse 1 korral saab ühendamiseks kasutada kompaktset USING süntaksi, mida lahenduse 2 korral ei saa kasutada.
  • "Hea SQL-i tava" ja semantiline selgus.
    • ON klausli eesmärk: See on mõeldud kirjeldama, kuidas tabelid on omavahel seotud (ehk tehniline seos, tavaliselt võtmed: hcv.facility_id = f.facility_id).
    • WHERE klausli eesmärk: See on mõeldud kirjeldama, milliseid andmeid tahetakse lõpptulemuses näha (ehk äriline filter: facility_name = 'Zoonder1267').
    • Kui kirjutada filtreeriv tingimus ON klauslisse (Lahendus 1), siis "peidetakse" äriloogika tehnilise ühendamise sisse. See teeb koodi lugemise teistele arendajatele raskemaks, sest nad peavad filtrit otsima JOIN tingimuste vahelt.
Kokkuvõte
Soovitus: Kasutage lahendus 1 (WHERE klausliga).
  • See on loetavam.
  • See eraldab selgelt tehnilise ühendamise (ON) ja ärilise filtreerimise (WHERE).
  • See vastab SQL-i standardsetele tavadele.

Kui kasutada lahenduses 2 INNER JOIN asemel OUTER JOIN, siis hakkab lause tagastama ebavajalikke tulemusi (lause muutub ebaõigeks).

Lahendus 3
SELECT hcv.*
FROM Health_care_visit AS hcv LEFT JOIN Facility ON hcv.facility_id=Facility.facility_id
AND facility_name='Zoonder1267';

Tulemus: Tagastab kõik visiidid (ka teistest asutustest).

Lahendus 4
SELECT hcv.*
FROM Health_care_visit AS hcv RIGHT JOIN Facility ON hcv.facility_id=Facility.facility_id
AND facility_name='Zoonder1267';

Tulemus: Nimekiri, kus on Zoonderi visiidid ja seejärel tuhanded read, kus on ainult tühjus (NULL), üks rida iga teise andmebaasis oleva asutuse kohta.

Seega on lahenduse 2 veel üks probleem selles, et sellel on muutmisel suurem vearisk, sest välisühendamise kasutuselevõtul muutub lause tulemus.

Ülesanne: Leia iga tervishoiuasutuse kohta, kui mitu visiiti on seal sellised, kus visiiditasu on väiksem kui 200.

Lahendus 1
SELECT Facility.facility_id, facility_name, Count(hcv.facility_id) AS cnt
FROM Facility LEFT JOIN Health_care_visit AS hcv ON hcv.facility_id=Facility.facility_id AND visit_fee<200
GROUP BY Facility.facility_id, facility_name
ORDER BY cnt DESC;
 
Lahendus 2
SELECT Facility.facility_id, facility_name, Count(hcv.facility_id) FILTER (WHERE visit_fee<200) AS cnt
FROM Facility LEFT JOIN Health_care_visit AS hcv ON hcv.facility_id=Facility.facility_id
GROUP BY Facility.facility_id, facility_name
ORDER BY cnt DESC;
 
Analüüs
A. Jõudlus (Performance)
  • Lahendus 1 (ON klauslis): See on nende andmetega rohkem kui kaks korda kiirem. PostgreSQL optimeerija filtreerib read välja enne ühendamist (JOIN). See vähendab mällu laetavate andmete hulka (Intermediate Result Set), mis läheb grupeerimisse (GROUP BY). Suurte andmemahtude korral on "varajane filtreerimine" alati parem.
  • Lahendus 2 (FILTER): Siin tehakse esmalt täielik LEFT JOIN (kõik visiidid ühendatakse asutustega) ja alles koondandmete leidmise faasis filtreeritakse välja need, mis on alla 200. See tähendab, et andmebaas töötleb alguses rohkem andmeid.
  • Hinnang: Lahendus 1 võidab.
B. Arusaadavus (Readability)
  • Lahendus 1: Tingimus visit_fee < 200 on peidetud JOIN ... ON klauslisse. See on tehniliselt korrektne, kuid semantiliselt segane. Kas see on ühendamise tingimus või äriline filter? LEFT JOIN puhul käitub see filtrina parempoolsele tabelile, säilitades vasaku, mida on paljudel intuitiivselt raske mõista.
  • Lahendus 2: See on väga selge. "Võta asutused ja visiidid. Loenda kokku (COUNT) need, kus tasu on alla 200." Äriloogika on eraldatud andmete sidumisest.
  • Hinnang: Lahendus 2 võidab. See on kaasaegne ja deklaratiivne.
C. Õpitavus (Learnability)
  • Lahendus 1: Algajal on raske aru saada, miks WHERE visit_fee < 200 (mis filtreeriks asutuse täiesti välja) ja AND visit_fee < 200 (mis jätab asutuse alles, aga nullib visiidi) käituvad erinevalt.
  • Lahendus 2: FILTER klausel on väga loomulik keeleline konstruktsioon.
  • Hinnang: Lahendust 2 on lihtsam õppida.
D. Ülekantavus (Portability)
  • Lahendus 1: See on standard SQL-92. See töötab absoluutselt enamikes andmebaasides (MySQL, Oracle, SQL Server, SQLite jne).
  • Lahendus  2: FILTER klausel (SQL:2003 standard) on toetatud näiteks PostgreSQL-is.
  • Hinnang: Lahendus 1 on universaalsem.

Soovitus:
PostgreSQL (18) keskkonnas eelistage lahendust 2 (FILTER).
Miks? Sest loetavus ja loogiline korrektsus (vigade vältimine) on tänapäeval tavaliselt tähtsamad kui väike võit jõudluses, välja arvatud juhul, kui tegemist on miljardite ridadega. Teine päring väldib elegantsemalt "null-ridade loendamise" probleemi.