Šajā rakstā detalizēti paskatīsimies uz veselu grupu tabulu kombinēšanas nosacījumu – OUTER JOIN.
Par latvisko nosaukumu
Kopš iepriekšējā raksta par Inner join mēģināju atrast kā tad latviski varētu šos tabulu savienojumu veidus iztulkot. Vienīgais, ko man izdevās atrast, ir tiešs tulkojums. Tātad tas būtu Ārējais savienojums priekš Outer join un Iekšējais savienojums Inner join. Pirmavoti ir norādīti augšminētā raksta pašās beigās. Ja kāds zin kaut ko labāku, citādāku vai apstiprina šos te, tad lūgtum apakšā komentāros teikt savu vārdu.
Ārējā savienojuma jēga un pielietojums
Atceramies, ko darīja Inner Join – atlasīja tos un tikai tos ierakstus no abām tabulām, kur abu tabulu ieraksti atbilst kombinēšanas nosacījumam. Taču ne vienmēr abās tabulās šādi ieraksti ir. Iespējams, ka ieraksti ir tikai vienā no abām tabulām, bet mēs vienalga gribam tos attēlot. Tas ir īstais gadījums, kad ir piemērots Outer Join, jo tas tiek lietots, lai atlasītu
- gan tos ierakstus no abām tabulām, kur abu tabulu ieraksti atbilst kombinēšanas nosacījumam,
- gan tos ierakstus no vienas vai abām tabulām, kuri neatbilst kombinēšanas nosacījumam, no otras tabulas ņemot kolonas ar NULL vērtībām.
Outer Join ir trīs veidi – Left, Right un Full. Left un Right attiecīgi saglabā ierakstus savienojuma kreisajā un labajā tabulā, savukārt, Full saglabā ierakstus abās tabulās. Iedomājamies, ka mums ir tabulas A un B, kā attēlots iepriekšējā rakstā. Tādā gadījumā katra ārējā savienojuma veida rezultāti grafiski izskatītos šādi:
Zīmējumā ar taisnstūri ir iezīmēts Iekšējā savienojuma rezultāts. Tātad redzam, ka Left Outer Join atstāj visus kreisās tabulas (Tabula A) ierakstus, un tos kurus iespējams, sakombinē ar labās puses (Tabula B) ierakstiem, bet pārējos atstāj ar NULL vērtībām labajā pusē. Right Outer Join dara tieši to pašu, ko Left Outer Join tikai samainot Tabulas A un B vietām. No tā, protams, arī izriet secinājums, ka saturiski ir vienalga kuru no tiem lieto, galvenais ir tabulas izvietot pareizajā pusē. Savukārt Full Outer Join mēģina sakombinēt tos ierakstus, kurus iespējams, un pārējos kreisās puses ierakstus (Tabula A) sakombinē ar NULL vērtībām labajā pusē, bet labās puses ierakstus (Tabula B) sakombinē ar NULL vērtībām kreisajā pusē.
Sintakse
SELECT <kolonu saraksts> FROM <kreisās puses tabula> [LEFT|RIGHT|FULL] [OUTER] JOIN <labās puses tabula> ON <kombinēšanas nosacījums>
Lielākā daļa, kas ir rakstīts par Inner Join (links) ir spēkā arī šeit, tāpēc tiem, kuri ir lasījuši rakstu par Inner Join varētu rasties Déjà vu sajūta 🙂
- SELECT un FROM klauzas ir aprakstītas iepriekšējā tēmā Vienkāršs SQL Select teikums.
- Atslēgas vārdu OUTER bieži vienkārši atmet un nelieto, bet tādā gadījumā LEFT, RIGHT vai FULL norāda to, ka tas ir ārējais savienojums un arī precizē, kurš konkrētais Outer Join veids.
- Pēc atslēgas vārda ON tiek rakstīts kombinēšanas nosacījums. Tas ne vienmēr ir tikai divu kolonu salīdzinājums uz vienādību, bet nosacījumus var kombinēt izmantojot loģiskos operatorus AND, OR, NOT.
Piemēri ir balstīti uz tām pašām tabulām un datiem, kas rakstā par CROSS JOIN.
Pirmajā piemērā izmantosim to pašu kombinēšanas nosacījumu, kas tika lietots Iekšējā savienojuma pirmajā piemērā. Kā redzams, arī tā persona, kam adrese nav norādīta (ārējās atslēgas lauks ir NULL) tiek attēlota rezultātu sarakstā.
Piemērs 1. Atlasa personas kodu, vārdu un adreses pilsētu katrai personai, saglabājot visas personas.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas 3 LEFT OUTER JOIN adreses ON prs_adr_id = adr_id; PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ------------ 12345678910 JĀNIS 23456789012 PĒTERIS TALSI 34567890123 ANNA TALSI 45678901234 KĀRLIS TALSI
Samainot Left ar Right vietām, varam redzēt, ka tiek attēlotas tikai tās personas, kam ir norādīta adrese, bet šajā reizē ir attēlotas arī tās adreses, kurās patiesībā neviena persona nedzīvo.
Piemērs 2. Atlasa personas kodu, vārdu un adreses pilsētu katrai personai, saglabājot visas adreses.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
2 FROM personas
3 RIGHT OUTER JOIN adreses ON prs_adr_id = adr_id;
PRS_PERSONA PRS_VARDS ADR_PILSETA
----------- ---------------------------------------- ------------
RĪGA
23456789012 PĒTERIS TALSI
45678901234 KĀRLIS TALSI
34567890123 ANNA TALSI
VALMIERA
Redzam, ka izmantojot to pašu kombinēšanas nosacījumu un Full Outer Join iegūst visus abu tabulu ierakstus.
Piemērs 3. Atlasa personas kodu, vārdu un adreses pilsētu katrai personai, saglabājot visas personas un visas adreses.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
2 FROM personas
3 FULL OUTER JOIN adreses ON prs_adr_id = adr_id;
PRS_PERSONA PRS_VARDS ADR_PILSETA
----------- ---------------------------------------- ------------
12345678910 JĀNIS
23456789012 PĒTERIS TALSI
34567890123 ANNA TALSI
45678901234 KĀRLIS TALSI
VALMIERA
RĪGA
Atšķirībā no Iekšējā savienojuma, ārējam savienojumam ir svarīgi, kur tiek rakstīti papildus nosacījumi – vai to dara kopā ar kombinēšanas nosacījumu, vai arī raksta WHERE klauzā. Šī tēma ir tik svarīga, ka tam ir izdalīta atsevišķa sadaļa.
Atšķirība starp papildus nosacījumiem kombinēšanas nosacījumā un WHERE klauzā
Skatamies, kas notiek, ja papildus nosacījumi tiek rakstīti WHERE klauzā. Tātad mēģinam atlasīt personas un to adreses, neatkarīgi no tā vai personai adrese ir vai nav norādīta un papildus pieliekam filtru, ka personas vārda otrais burts ir Ā.
Piemērs 4. Atlasa personas kodu, vārdu un adreses pilsētu saglabājot personas ar papildus nosacījumu WHERE klauzā.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas 3 LEFT OUTER JOIN adreses ON prs_adr_id = adr_id 4 WHERE prs_vards LIKE '_Ā%'; PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ------------ 12345678910 JĀNIS 45678901234 KĀRLIS TALSI
Salīdzinam rezultātu ar to, kas bija pirmajā piemērā. Kā redzam loģiski – palicis Jānis ar Kārli, bet Pēteris un Anna no rezultātu kopas ir izmesti ārā, jo šajos vārdos otrais burts nav Ā. Tagad skatamies, kas notiks, ja šo nosacījumu par otro burtu Ā pārvietosim uz kombinēšanas nosacījumu.
Piemērs 5. Atlasa personas kodu, vārdu un adreses pilsētu saglabājot personas ar papildus kombinēšanas nosacījumu.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas 3 LEFT OUTER JOIN adreses ON prs_adr_id = adr_id 4 AND prs_vards LIKE '_Ā%'; PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ------------- 12345678910 JĀNIS 23456789012 PĒTERIS 34567890123 ANNA 45678901234 KĀRLIS TALSI
Hopsā! Esam dabūjuši visas personas, tai skaitā arī tādas, kurām otrais burts nemaz nav Ā! Kas tad nu? Nāksies vien apskatīties sīkāk, kā Outer joini teorētiski veidojas:
1. Tiek izveidots iesaistīto tabulu Dekarta reizinājums, t.i. abu tabulu ierakstu visas iespējamās kombinācijas. Šis lūk ir kārtējais iemesls kāpēc būtu jāzin, kas tas Dekarta reizinājums tāds ir.
2. Katram ierakstam, ko izveidojis Dekarta reizinājums, tiek veikta kombinēšanas nosacījuma pārbaude:
2.1. Ja kombinēšanas nosacījuma rezultātā iegūst vērtību Patiess, tad ieraksts tiek saglabāts rezultāta kopā.
2.2. Ja kombinēšanas nosacījuma rezultātā iegūst vērtību Aplams, tad ieraksts vienalga tiek saglabāts, bet kolonas no tās tabulas, kurā ieraksti jāsaglabā (piemērā kreisās puses tabula personas), tiek atstātas, bet kolonas no tās tabulas, kurā ieraksti nav jāsaglabā tiek uzstādītas par NULL.
3. No soļa 2.2. rezultātā radītajiem ierakstiem, tiek atstāti tikai unikālie ieraksti.
4. Visbeidzot tiek izpildīta Where klauza un atstāti tikai tie ieraksti, kas tai atbilst.
Tātad atbilde tam, kāpēc 5. piemērā tika saglabāts arī Pēteris un Anna, ir meklējama 2.2. solī, kad Dekarta reizinājuma rezultātā iegūtais ieraksts tiek saglabāts, kaut gan kombinēšanas nosacījums ir aplams. Tam gan tiek uzstādītas NULL vērtības kolonām, kas ņemtas no labās tabulas, jo šīs tabulas ieraksti nav jāsaglabā.
Pārējie piemēri
Gluži tāpat, kā ar Inner join, ja kombinēšanas nosacījums vienmēr ir patiess, tad esam atpakaļ pie Dekarta reizinājuma. Šoreiz pārmaiņas pēc paņemsim Right outer join.
Piemērs 6. Ja nosacījums vienmēr ir patiess, tad OUTER JOIN degradējās uz CROSS JOIN (Dekarta rezinājumu).
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas 3 RIGHT OUTER JOIN adreses ON 1=1; PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ------------ 12345678910 JĀNIS RĪGA 23456789012 PĒTERIS RĪGA 34567890123 ANNA RĪGA 45678901234 KĀRLIS RĪGA 12345678910 JĀNIS TALSI 23456789012 PĒTERIS TALSI 34567890123 ANNA TALSI 45678901234 KĀRLIS TALSI 12345678910 JĀNIS VALMIERA 23456789012 PĒTERIS VALMIERA 34567890123 ANNA VALMIERA 45678901234 KĀRLIS VALMIERA
7. piemērā ir parādīts, ka kombinēšanas nosacījumam ne vienmēr ir jābūt vienādībai. Šajā gadījumā tiek atlasītas personas, kuru adrešu identfikatori nesakrīt ar adreses identifikatoru adreses tabulā. Atceramies, ka ārējo savienojumu gadījumā tika atlasīti arī tie ieraksti, kam kombinēšanas nosacījums ir aplams, vienkārši kolonas no nesaglabājamās tabulas tiek uzstādītas par NULL. Attiecīgi piemērā pirmā rindiņa, kas ir izcelta treknrakstā, ir papildus tam, ko dabūtu izmantojot iekšējo savienojumu tādā pašā piemērā.
Piemērs 7. Nosacījums var arī nebūt vienādība. Atlasam tos ierakstus no personām, kam adreses identifikators nesakrīt ar identifikatoru adreses tabulā.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas 3 LEFT JOIN adreses ON prs_adr_id <> adr_id 4 / PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ----------- 12345678910 JĀNIS 23456789012 PĒTERIS RĪGA 23456789012 PĒTERIS VALMIERA 34567890123 ANNA RĪGA 34567890123 ANNA VALMIERA 45678901234 KĀRLIS RĪGA 45678901234 KĀRLIS VALMIERA
Atceramies, ka pie iekšējiem savienojumiem bija piemērs par tabulas kombinēšanu pašu ar sevi. Tur atlasītais rezultāts bija visai nabadzīgs, jo tika atlasīti tikai tie ieraksti, kur bērnam ir gan māte, gan tēvs. Lai iegūtu iespējams nepilnīgu informāciju par katru personu, var izmantot ārējos savienojumus. Tātad trīs pirmās rindiņas treknrakstā ir tās, kas ir nākušas klāt Inner join nomainot ar Left [outer] join.
Piemērs 8. Piemērs kombinēšanai tabulai ar sevi (SELF JOIN) un vairāku tabulu kombinēšanai vienā SQL teikumā.
SQL> SELECT
2 tevs.prs_vards "Teva Vārds",
3 mate.prs_vards "Mātes vārds",
4 berns.prs_vards "Bērna vārds"
5 FROM personas berns
6 LEFT JOIN personas tevs ON berns.prs_tevs_id = tevs.prs_id
7 LEFT JOIN personas mate ON berns.prs_mate_id = mate.prs_id;
Teva Vārds Mātes vārds Bērna vārds
----------- ------------- ------------
JĀNIS
JĀNIS PĒTERIS
ANNA
PĒTERIS ANNA KĀRLIS
Ārējo savienojumu alternatīvā sintakse Oraclē
Pirms vēl ANSI bija sasparojies un standartizējis ārējo savienojumu veidus un sintaksi, Oraclē tie jau bija pieejami. Protams, kā vienmēr pirmajiem nākas maksāt – šis kombinēšanas sintakses veids neatbilst standartam un tam ir mazāk iespēju, kā jaunajam. Bet tas aizvien vēl joprojām darbojās un to var izmantot. Tātad sintakse ir tāda pati kā Inner join alternatīvā sintakse, bet papildus pie tabulas kolonām, kas tiek kombinēta klāt pamattabulai ārējā savienojumā, kombinēšanas nosacījumā ir jāliek (+):
SELECT <kolonu saraksts> FROM <kreisās puses tabula>, <labās puses tabula> WHERE <kombinēšanas nosacījums>
Skatamies piemēru, kas ir ekvivalents šī paša raksta pirmajam piemēram ar Left outer join.
Piemērs 9. Šis ir ekvivalents piemēram 1, kurā izmantots Left join. Atlasa personas kodu, vārdu un adreses pilsētu katrai personai, saglabājot visas personas lietojot Oracle outer join specifisko operatoru (+).
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta 2 FROM personas, adreses 3 WHERE prs_adr_id = adr_id (+); PRS_PERSONA PRS_VARDS ADR_PILSETA ----------- ---------------------------------------- ------------ 12345678910 JĀNIS 23456789012 PĒTERIS TALSI 34567890123 ANNA TALSI 45678901234 KĀRLIS TALSI
Piemērs 10. Šis ir ekvivalents piemēram 2, kurā izmantots Right join. Atlasa personas kodu, vārdu un adreses pilsētu katrai personai, saglabājot visas adreses lietojot Oracle outer join specifisko operatoru (+).
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
2 FROM personas, adreses
3 WHERE prs_adr_id (+) = adr_id;
PRS_PERSONA PRS_VARDS ADR_PILSETA
----------- ---------------------------------------- ------------
RĪGA
23456789012 PĒTERIS TALSI
45678901234 KĀRLIS TALSI
34567890123 ANNA TALSI
VALMIERA
Oracle Outer join operatoram ir vairāki ierobežojumi, kas nav modernākajai ANSI join sintaksei, piemēram:
- Tas nenodrošina pilnu ārējo savienojumu (full outer join);
- To nevar lietot kopā ar moderno sintaksi;
- Kombinēšanas nosacījumā nevar izmantot OR operatoru u.c.
Pārējās bieži izplatītās kļūdas, kas attiecas uz Oracle outer join operatoru, skatīt Turpmākajā lasāmvielā rakstā Common errors seen when using OUTER-JOIN.
Microsoft SQL Server arī ir līdzīgi vēsturiski Outer join operatori (*= un =*), kas gan jaunākās versijās bez papildus pūlēm nav pielietojami.
Turpmākā lasāmviela
- What’s in a Condition? by Jonathan Gennick – apraksts par atšķirību starp nosacījumu, kas ir kombinēšanas nosacījumā, un nosacījumu, kas ir where klauzā.
- Common errors seen when using OUTER-JOIN by Kevin Meade – kļūdas, kādas visbiežāk mēdz pielaist lietojot iepriekšējo Oracle Outer join sintaksi (izmantojot (+) zīmi).
Datubāzes 
gribu nedaudz pakomentēt nodaļas “Atšķirība starp papildus nosacījumiem kombinēšanas nosacījumā un WHERE klauzā” piemēriem.
Piemērs ir izdevies, ja gribam parādīt, ka ir jāmācās atbilstoši gadījumam nosacījumus rakstīt gan pie join, gan where, bet nodaļā ir liels trūkums. Te prasās pēc piemēra, kurā parādīts ko darīt, ja ar left join pievienotajā tabulā ir nepieciešami vairāki nosacījumi (ne tikai pēc ID, bet, piemēram, arī pēc pilsētas). Arī tad ir jāsaprot, ka mainot nosacījuma atrašanās vietu, mainās rezultāts.
principā to palīdz saprast apraksts “kā Outer joini teorētiski veidojas”, bet tomēr, ja mēs šo uzskatām par vietu, kur meklēt atbildes uz jautājumiem SQL jomā, tad piemērs tomēr derētu.