Tutorial: Joins In Mysql

Join in MySQL In deze tutorial haal ik een uitermate interessante techniek uit de relationele database aan. Ik heb het hier over join. Deze techniek is interessant omdat je op deze manier 2 of meer tabellen aan elkaar kan koppelen doormiddel van ‘foreign keys’ en zo het resultaat in één tabel kan weergeven. Ik hoef niet meer te vertellen dat een mysql-query uitvoeren in een phpscript nogal wat tijd vraagt. Je kan dus beter met één query zoveel mogelijk informatie ophalen. Daarom deze tutorial. Ik splits het joinen op zoals het ook in SQL gedaan wordt: o inner join o outer join o self join In SQL maakt men dan nog onderscheid tussen equi en non-equi join alhoewel die wel erg hard aansluiten bij inner join, behandel ik ze niet in deze tutorial.

Inleiding MySQL doet niet echt moeilijk in het afhandelen van primary en foreign keys. Zeker niet bij de creatie van een tabel. Vele beginnende programmeurs houden er dan ook geen rekening mee, en linken tabellen aan elkaar in hun script. Laat dat nu even niet de bedoeling zijn van een relationele database en je via joinen veel tijd en script bespaard. Voor de echte beginners: Primary Key of primaire sleutel is een veld van een tabel in een database die voor iedere ingevoerde rij uniek MOET zijn, zonder uitzondering. Velen gebruiken voor websites gewoon een veld ‘id’. Doormiddel van de optie ‘auto_increment’ blijft deze dan nog uniek ook. Er bestaan ook gedeelde primaire sleutels wat wil zeggen dat twee velden gecombineerd worden zodanig dat hierdoor iedere rij zeker uniek is. Ik ga hier niet verder op in, maar weet dat het bestaat. Foreign key of vreemde sleutel is een veld van een tabel in een database welke de waarde wijst naar een veld in een andere tabel. Dit is meestal de primary key omdat er dan zeker geen verwarring mogelijk is. Duidelijk voorbeeld is hierbij een reactiesysteem waarbij de reacties in één tabel staan en een veld hebben waarin de id van het bericht staat waarop dit bericht een reactie is. Vele beginnende programmeurs gebruiken deze technieken wel maar vergeten ze te declareren bij het aanmaken van de tabel. De primary key dat lukt nog wel, maar de foreign key meestal niet. Mysql maakt daar ook geen probleem van, maar als je nu net goed wil gaan ‘joinen’ dan is dit wel nodig. De syntax is alsvolgt: Naam_veld type references tabel(veld) Of in voorbeeld:

d id i n t (4 ) not nu l l re fe rences ber i ch t ( i d )

Waarin het veld ‘did’ verwijst naar de id van de tabel bericht. Jammer genoeg gaat dit echter over een CREATE statement. Jouw tabellen zijn waarschijnlijk al een eeuwigheid aangemaakt en ingevuld. Daarom even de code om je

tabel aan te passen. Dit gebeurt met het commando ALTER waarover ook nog een reeks tutorials over geschreven zouden kunnen worden, maar je hebt ze nu niet iedere dag nodig, vandaar dat ik even dit korte statement oplicht. ALTER TABLE tabel_naam ADD FOREIGN KEY(naam_veld_fk) REFERENCES tabel_naam(veld);

Even in een voorbeeld:

ALTER TABLE reactie ADD FOREIGN KEY(did) REFERENCES bericht(id);

Waarin de tabel reactie wordt aangepast zodat het veld ‘did’ wijst naar het veld ‘id’ in de tabel bericht. Voila, bij deze zijn de foreign key geïnstalleerd. Nogmaals, meestal vormt mysql geen problemen wanneer je niet gelinkte tabellen toch zou joinen. Het gebeurt alleen in zeer specifieke gevallen dat je resultaat niet werkt. Ik denk hierbij aan iets wat ik zelf heb geprogrammeerd met een outer join.

Inner Join Goed, na deze kleinigheden, het echte werk. Het aan elkaar koppelen van tabellen. Ik gebruik in deze tutorial maar even drie voorbeeldtabellen uit een nabij gelegen praktijk. Een tabel met berichten, een tabel met reacties op die berichten en een tabel met leden die de berichten schrijven:

In dit voorbeeld is voortzetting het vervolg op een eerder geplaatst bericht. Indien het bericht zo’n vervolg is, staat het nummer van het voorgaande bericht in voortzetting. Is dit bericht geen voortzetting is het veld NULL. Zo kan je nu bijvoorbeeld met 1 query alle bericht ophalen met daarbij de naam en email van de schrijver. Dit gebeurt zo: SELECT b.Id, b.Titel, b.Bericht, l.Naam, l.Email FROM Bericht AS b, Lid AS l WHERE b.Schrijver = l.Lidnr

De Join gebeurt bij een inner join in de where clause. Doormiddel van een gelijkheidsteken worden de velden aangeduid die gelijk moeten zijn (equi-join). In dit geval wordt dus bij een bericht meteen de naam en email van de schrijver in de resultaten tabel geplaatst. In de From lijn worden nodige tabellen aangehaald. Doormiddel van AS kan je een afkoring definiëren om zo het typwerk te beperken. Doe je dat niet, dan moet je in plaats van de letters de volledige tabelnaam typen. Haal je nu het resultaat uit je query doormiddel van een mysql_fetch_array() in php dan kan je gewoon de tabelnamen gebruiken in de array:
while($fetch_arr = mysql_fetch_array($query)){ // Overbrengen van query naar array. echo $fetch_arr[titel] . “geschreven door”. $fetch_arr[naam]; } ?>

Wanneer je nu de joinclausule zou vergeten in je query (geen where dus, of een foutieve) krijg je een cartesiaans product. Dat wil zeggen dat voor ieder bericht in de kolom alle leden worden weergegeven. Je krijgt dan zoiets:

Een resultaat waar vrijwel nooit iets mee aan te vangen valt, er blijken wel toepassingen te zijn waar dit gebruikt kan worden. Maar ik kan zo niet meteen een voorbeeld aanhalen.

Outer Join Inner Join hebben echter één gigantisch nadeel. Wanneer nu uit het vorige voorbeeld blijkt dat schrijver NULL is, omdat de persoon in kwestie niet ter zake doet of liever onbekend blijft, wordt de rij niet weergegeven. Met andere woorden, berichten zonder schrijver, worden niet weergegeven. Om dit probleem op te lossen is er de outer join. Mensen die al eens met oracle werken kennen dit principe door (+) toe te voegen aan een bepaalde tabel. In MySQL kan dit niet en gebruik je LEFT/RIGHT OUTER JOIN.

In een outer join komt het er eigenlijk op neer dat je aan één van de tabellen die je joint een tijdelijke rij gaat toevoegen waarin alle velden NULL zijn. Hierdoor kan je join toch doorgaan. Voor je hieraan begint moet je even stilstaan welke tabel er die extra NULL-rij nodig heeft. In ons voorbeeld van daarnet is dat de tabel ‘lid’. Aangezien die tabel de oorzaak is van het niet weergeven van een gejoinde rij. M.a.w. de foreign key wijst naar die tabel.

De code is dan als volgt:

SELECT b.Id, b.Titel, b.Bericht, l.Naam, l.Email FROM Bericht AS b LEFT JOIN Lid AS l ON b.Schrijver = l.Lidnr

Hierin wordt bericht gelinkt aan lid, waar bij lid een ‘NULL-rij’ wordt toegevoegd om zo zelfs de berichten waar geen schrijver aan gelinkt is weer te geven. Er bestaat ook nog zoiets als een right-join. De MySQL-manual raadt u aan de left join te gebruiken. De andere heeft eerder te maken met welke tabel je nu echt volledig wil weergeven in de joinconditite (na ON).

Self Join In een self join wordt een tabel, zoals het woord al zegt, gelinkt met zichzelf. Andere benaming is Autojoin, voor eventuele verwarring te vermijden. Ook dit kan ik duidelijk aantonen doormiddel van het eerder gebruikte voorbeeld. Zoals je wel al kan raden gebeurt dat in ‘bericht’, de tabel bevat het veld ‘voortzetting’ die wijst naar een andere rij in dezelfde tabel. Om deze tabellen te linken gebruikt men dezelfde structuur als een inner join. Let erop dat je nu dezelfde tabel twee maal opsomt in de from lijn. SELECT ouder.Bericht “eerste bericht”, kind.Bericht “vervolg” FROM Bericht AS ouder, Bericht AS kind WHERE ouder.Id = kind.Voortzetting

Waarin een resultatentabel gecreëerd wordt met twee kolommen, in de eerste staat het eerste bericht, in de tweede het vervolg. Ik heb voor de duidelijkheid aliassen gekozen met ouder en kind, uiteraard kan je hier ook letters gebruiken. Let ook hier op dat een deel van je rijen zal wegvallen wanneer geen enkel bericht erop voortzet en het zelf ook geen voortzetting is. Wil je dit vermijden dan zal je een outer join moeten gebruiken. Lees daarvoor het voorgaande hoofdstuk nog maar eens goed.

Slot Goed tot zover, ik geef toe het is een hele brok. Maar eens je het door hebt, is dit zoveel makkelijker dan de tabellen linken in je script of voor ieder bericht een nieuwe query uit te voeren om de schrijver te vinden. Hopelijk had u er wat aan en besef je nogmaals dat MySQL heel wat meer in z’n mars hebt dan je in eerste instantie denkt. Vragen en opmerkingen mag je me zeker en vast toesturen. Veel succes!