Tutorials
SQL Joins [Uitgebreid]
Een uitleg over het werken en gebruiken van JOINS in MySQL om gegevens uit meerdere tabellen te halen en te koppelen in je query. Je kan dus aan elkaar gerelateerde gegevens ophalen, waarbij de relatie gemaakt wordt zoals jij wil.t
Pagina 1
Inleiding
Wanneer je al wat langer bezig bent met PHP en (My)SQL krijg je ook wel eens moeilijkere problemen voor de kiezen. Bijvoorbeeld wanneer je 2 tabellen met elkaar wilt koppelen. Dit kan natuurlijk met subquerys of met een WHERE clausule waarin de kolommen die overeen moeten komen worden vergeleken. Echter zullen deze manieren niet altijd uitkomst kunnen brengen, dus is hiervoor de JOIN in het leven geroepen. De kans is heel klein dat er een SQL versie (maakt niet uit of het PostgreSQL of MySQL of nog een andere SQL variant is) bestaat waarin je de JOIN niet kan gebruiken. (Deze opmerking heb ik geplaats naar aanleiding van een vraag hierover in de reacties)
Het gebruikte voorbeeld van de coureurs komt voor een deel van Sitemasters, de informatie is wel allemaal door mezelf getypt en bedacht. Verder heb ik het idee wat verder uitgewerkt, om te kunnen laten zien dat je ook met meer dan 2 tabellen joins kan maken.
(mochten jullie nog aanvullingen hebben, schroom niet om ze te pm-en of te posten, ik zal dan de tutorial bijwerken)
Aanleiding:
De aanleiding voor het schrijven van deze tutorial kwam eigenlijk door een vraag van Elsy, waarin een JOIN was gebruikt (de INNER JOIN). De bedoeling van de query was dat deze zowel de Categoriën uit de ene tabel haalde en subcategorieën uit de andere. Nu kreeg ze door het verkeerde type JOIN alleen de categorieën waar wel een subcategorie onder viel.
Toen bedacht ik mij dat het wel heel handig zou zijn als daar een tutorial voor kwam, omdat de JOIN een nette en gemakkelijke manier is om gegevens te halen uit 2 gekoppelde tabellen.
Opmerking:
Deze tutorial is een uitgebreide versie van de vorige, de vorige wordt binnenkort verwijderd!
Het gebruikte voorbeeld van de coureurs komt voor een deel van Sitemasters, de informatie is wel allemaal door mezelf getypt en bedacht. Verder heb ik het idee wat verder uitgewerkt, om te kunnen laten zien dat je ook met meer dan 2 tabellen joins kan maken.
(mochten jullie nog aanvullingen hebben, schroom niet om ze te pm-en of te posten, ik zal dan de tutorial bijwerken)
Aanleiding:
De aanleiding voor het schrijven van deze tutorial kwam eigenlijk door een vraag van Elsy, waarin een JOIN was gebruikt (de INNER JOIN). De bedoeling van de query was dat deze zowel de Categoriën uit de ene tabel haalde en subcategorieën uit de andere. Nu kreeg ze door het verkeerde type JOIN alleen de categorieën waar wel een subcategorie onder viel.
Toen bedacht ik mij dat het wel heel handig zou zijn als daar een tutorial voor kwam, omdat de JOIN een nette en gemakkelijke manier is om gegevens te halen uit 2 gekoppelde tabellen.
Opmerking:
Deze tutorial is een uitgebreide versie van de vorige, de vorige wordt binnenkort verwijderd!
Pagina 2
Voorbeeld
Onderstaande tabelindeling zal ik in de rest van de tutorial gebruiken om het 1 en ander uit te werken. Ik heb het oude voorbeeld wat uitgebreid, om zo met meer dan 2 (in dit geval 4) tabellen ook een JOIN te kunnen laten zien.
Bij de tabel ‘coureur_stal’ staan een start en einddatum, zodat je ook kan zien voor welke renstallen een coureur al eerder heeft gereden. Het voorbeeld gaat over coureurs die al dan niet in een bepaald team zitten, en teams die al dan niet coureurs in hun renstal hebben. Dus niet iedere coureur hoeft lid te zijn van een renstal. is lid van een renstal, maar ook niet iedere renstal heeft een coureur om een auto te rijden. Deze datum zal in de voorbeeld uitwerkingen niet verder worden gebruikt. Ik heb het er bijgevoegd, om te laten zien dat het wel een genormaliseerde tabelindeling is. Zou ik de start- en einddatum niet hebben toegevoegd, dan had er geen koppeltabel hoeven zijn, maar had ik het renstal_id in de tabel ‘coureurs’ moeten zetten.
Verder ga ik er in het voorbeeld vanuit dat er maar 1 racebaan per land is, zodat er geen aparte tabel met landen hoeft te komen.
TABEL coureurs:
- id (1, 2, 3, 4, 5, 6)
- coureur (Jansen, Schumacher, Verstappen, Rubens, Heins, Markus)
- leeftijd (41, 39, 23, 34, 35, 24)
TABEL renstal:
- id (1,2,3,4)
- naam (Snel, Sneller, Snelst, Allersnelst)
TABEL racebaan:
- id (1,2,3,4,5)
- land (Nl, De, GB, Bra, Be)
- thuisbaan_van (1,3,NULL,NULL,2)
TABEL coureur_stal:
- id (1,2,3,4)
- coureur_id (1,6,4,2)
- renstal_id (1,2,3,4)
- startdatum
- einddatum
Zoals aangegeven wordt de datum verder niet gebruikt, hier heb ik dus ook geen waarde aan meegegeven
Bij de tabel ‘coureur_stal’ staan een start en einddatum, zodat je ook kan zien voor welke renstallen een coureur al eerder heeft gereden. Het voorbeeld gaat over coureurs die al dan niet in een bepaald team zitten, en teams die al dan niet coureurs in hun renstal hebben. Dus niet iedere coureur hoeft lid te zijn van een renstal. is lid van een renstal, maar ook niet iedere renstal heeft een coureur om een auto te rijden. Deze datum zal in de voorbeeld uitwerkingen niet verder worden gebruikt. Ik heb het er bijgevoegd, om te laten zien dat het wel een genormaliseerde tabelindeling is. Zou ik de start- en einddatum niet hebben toegevoegd, dan had er geen koppeltabel hoeven zijn, maar had ik het renstal_id in de tabel ‘coureurs’ moeten zetten.
Verder ga ik er in het voorbeeld vanuit dat er maar 1 racebaan per land is, zodat er geen aparte tabel met landen hoeft te komen.
TABEL coureurs:
- id (1, 2, 3, 4, 5, 6)
- coureur (Jansen, Schumacher, Verstappen, Rubens, Heins, Markus)
- leeftijd (41, 39, 23, 34, 35, 24)
TABEL renstal:
- id (1,2,3,4)
- naam (Snel, Sneller, Snelst, Allersnelst)
TABEL racebaan:
- id (1,2,3,4,5)
- land (Nl, De, GB, Bra, Be)
- thuisbaan_van (1,3,NULL,NULL,2)
TABEL coureur_stal:
- id (1,2,3,4)
- coureur_id (1,6,4,2)
- renstal_id (1,2,3,4)
- startdatum
- einddatum
Zoals aangegeven wordt de datum verder niet gebruikt, hier heb ik dus ook geen waarde aan meegegeven
Pagina 3
Inner Join
De INNER JOIN gebruik je alleen wanneer je zeker weet dat er een relatie is tussen de gegevens in de tabellen. Stel dat je wil weten wie wat eet, en alleen gegevens wil zien van de personen die ook werkelijk iets eten volgens de tabel. Ik zal het resultaat toelichten aan de hand van het voorbeeld:
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
INNER JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Deze query zal het volgende aan gegevens terug geven:
Snel -> Nl
Sneller -> De
Snelst -> Be
Zoals je ziet komt de renstal die geen thuisbaan heeft niet voor in de uitkomt. Ook komen de racebanen die geen team hebben waarvan het de thuisbaan is niet voor.
Het resultaat van de INNER JOIN geeft allen gegevens terug die gekoppeld kunnen worden via de 2 koppeltabellen
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
INNER JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Deze query zal het volgende aan gegevens terug geven:
Snel -> Nl
Sneller -> De
Snelst -> Be
Zoals je ziet komt de renstal die geen thuisbaan heeft niet voor in de uitkomt. Ook komen de racebanen die geen team hebben waarvan het de thuisbaan is niet voor.
Het resultaat van de INNER JOIN geeft allen gegevens terug die gekoppeld kunnen worden via de 2 koppeltabellen
Pagina 4
Outer Join
In de in de vorige delen van deze tutorial viel er altijd een deel van één van de tabellen weg. Dit komt door de koppeling die door de join wordt gemaakt. Wil je toch alle gegevens hebben uit beide tabellen, ongeacht of er een koppeling is te maken, dan gebruik je de OUTER JOIN. Je kan dit het beste zien alsof de 2 tabellen worden samengevoegd tot 1 tabel, waarbij alle gegevens wel blijven bewaard.
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
OUTER JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze query is als volgt:
Snel -> Nl
Sneller -> De
Snelst -> Be
NULL -> GB
NULL -> Bra
Allersnelst -> NULL
Zoals je ziet heb je nu alle gegevens uit beide tabellen samengevoegd in het resultaat en zijn de 2 tabellen eigenlijk 1 geworden (dit gebeurt wel alleen met de kolomen die je bij het SELECT statement hebt opgegeven!!)
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
OUTER JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze query is als volgt:
Snel -> Nl
Sneller -> De
Snelst -> Be
NULL -> GB
NULL -> Bra
Allersnelst -> NULL
Zoals je ziet heb je nu alle gegevens uit beide tabellen samengevoegd in het resultaat en zijn de 2 tabellen eigenlijk 1 geworden (dit gebeurt wel alleen met de kolomen die je bij het SELECT statement hebt opgegeven!!)
Pagina 5
Binnen 1 tabel
In de voorgaande stukken van de tutorial hebben we telkens een koppeling gemaakt tussen items van 2 of meer verschillende tabellen. Het is echter ook mogelijk om een join te doen binnen eenzelfde tabel. Dit kan in sommige gevallen zeer handig zijn. Het voorbeeld hieronder heeft niets te maken met het voorbeeld van de coureurs en renstallen.
Stel je hebt een tabel, voor een menu. Daarin heb je een ParentId staan bij de verschillende menu items, om aan te geven dat een item onder een bepaald hoofditem valt. Om nu hier de juiste gegevens uit te krijgen kan je een subquery maken, waarmee je de gegevens aan elkaar koppelt, maar het kan ook gemakkelijker: Met een JOIN
Stel de tabel ziet er zo uit:
id (1,2,3,4,5,6,7,8)
naam -> niet opgegeven, is niet direct van belang
parent (NULL,NULL,NULL,3,3,2,1,2)
Dit kan je realiseren door een outer join toe te passen op 1 tabel. Het lijkt allemaal wat raar, omdat je het in eenzelfde tabel doet, maar zo krijg je een keurig overzicht van je menu:
Resultaat:
Child -> Parent
1 -> NULL
2 -> NULL
3 -> NULL
4 -> 3
5 -> 3
6 -> 2
7 -> 1
8 -> 2
Je krijgt nu keurig een lijstje met de gekoppelde waarden terug. Waarbij je alleen de submenu-items te zien krijgt. Door een JOIN toe te passen op 1 tabel kan je, mits je het juist doet keurig het menu op de juiste volgorde ophalen uit de tabel.
Stel je hebt een tabel, voor een menu. Daarin heb je een ParentId staan bij de verschillende menu items, om aan te geven dat een item onder een bepaald hoofditem valt. Om nu hier de juiste gegevens uit te krijgen kan je een subquery maken, waarmee je de gegevens aan elkaar koppelt, maar het kan ook gemakkelijker: Met een JOIN
Stel de tabel ziet er zo uit:
id (1,2,3,4,5,6,7,8)
naam -> niet opgegeven, is niet direct van belang
parent (NULL,NULL,NULL,3,3,2,1,2)
Dit kan je realiseren door een outer join toe te passen op 1 tabel. Het lijkt allemaal wat raar, omdat je het in eenzelfde tabel doet, maar zo krijg je een keurig overzicht van je menu:
SELECT
hoofdmenu.id, submenu.parent
FROM
menu AS hoofdmenu
OUTER JOIN
menu AS submenu
ON
hoofdmenu.id = submenu.parent
Resultaat:
Child -> Parent
1 -> NULL
2 -> NULL
3 -> NULL
4 -> 3
5 -> 3
6 -> 2
7 -> 1
8 -> 2
Je krijgt nu keurig een lijstje met de gekoppelde waarden terug. Waarbij je alleen de submenu-items te zien krijgt. Door een JOIN toe te passen op 1 tabel kan je, mits je het juist doet keurig het menu op de juiste volgorde ophalen uit de tabel.
Pagina 6
Left en Right Join
Zoals in het vorige deel stuk al duidelijk was werden niet alle renstallen weergegeven in het resultaat. Wil je alle renstallen weergeven, of ze nu een thuisbaan hebben of niet, dan gebruik je in dit geval een LEFT JOIN.
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
LEFT JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze LEFT JOIN query ziet er als volgt uit:
Snel -> Nl
Sneller -> De
Snelst -> Be
Allersnelst -> NULL
Je hebt nu alle renstallen op een rijtje, en als er een thuisbaan is voor ze dan is die ook aangegeven. Maar wat als je alle landen weer wilt geven, met de daarbij horende renstal?
Je kan dan 2 dingen doen. Je kan de volgorde van de tabellen veranderen in je query (dan wisselen de regel voor LEFT JOIN en de regel daarna van plaats) of je maakt van de LEFT JOIN een RIGHT JOIN.
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
RIGHT JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze RIGHT JOIN query ziet er als volgt uit:
Snel -> Nl
Sneller -> De
Snelst -> Be
NULL -> GB
NULL -> Bra
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
LEFT JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze LEFT JOIN query ziet er als volgt uit:
Snel -> Nl
Sneller -> De
Snelst -> Be
Allersnelst -> NULL
Je hebt nu alle renstallen op een rijtje, en als er een thuisbaan is voor ze dan is die ook aangegeven. Maar wat als je alle landen weer wilt geven, met de daarbij horende renstal?
Je kan dan 2 dingen doen. Je kan de volgorde van de tabellen veranderen in je query (dan wisselen de regel voor LEFT JOIN en de regel daarna van plaats) of je maakt van de LEFT JOIN een RIGHT JOIN.
<?php
$query="
SELECT
r.naam
rb.land
FROM
renstal AS r
RIGHT JOIN
racebaan coureurs AS rb
ON
rb.thuisbaan_van = r.id";
?>
Het resultaat van deze RIGHT JOIN query ziet er als volgt uit:
Snel -> Nl
Sneller -> De
Snelst -> Be
NULL -> GB
NULL -> Bra
Pagina 7
Meer dan 2 tabellen
Het is met de JOIN ook prima mogelijk om op meerdere tabellen te JOINEN. Dit is het gemakkelijkst uit te leggen aan de hand van een voorbeeld uit onze coureurs/ renstal tabellen.
Stel dat je de thuisbaan van een coureur wilt weten. Hiervoor hebben we de volgende tabellen nodig: (ik heb ze voor dit voorbeeld wat aangepast, omdat je anders met de datum rekening moet houden)
TABEL coureurs:
- id (1, 2, 3, 4, 5, 6)
- coureur (Jansen, Schumacher, Verstappen, Rubens, Heins, Markus)
- leeftijd (41, 39, 23, 34, 35, 24)
- renstal (1,NULL,NULL,3,4,NULL)
TABEL renstal:
- id (1,2,3,4)
- naam (Snel, Sneller, Snelst, Allersnelst)
TABEL racebaan:
- id (1,2,3,4,5)
- land (Nl, De, GB, Bra, Be)
- thuisbaan_van (1,3,NULL,NULL,2)
Hier zijn 3 tabellen voor nodig, zoals je ziet. Om de werking van het gebruik van meerdere JOINS in 1 query goed te kunnen begrijpen, moet je onthouden dat een JOIN 2 tabellen samenvoegt tot 1 nieuwe tabel.
<?php
$query= "SELECT
c.naam,
rb.land
FROM
coureurs AS c
INNER JOIN
(racebaan AS rb
INNER JOIN
renstal AS rs
ON
rs.id = rb.thuisbaan_van)
ON
rs.id =c.renstal";
?>
Je mag in deze voorbeeldquery stellen dat dit deel:
(racebaan AS rb
INNER JOIN
renstal AS rs
ON
rs.id = rb.thuisbaan_van)
Een nieuwe tabel vormt, uit die 2 tabellen, waar je vervolgens weer een join op kan toepassen. Op deze manier kan je je query uitbreiden tot een grote query met een groot aantal joins.
Het resultaat van bovenstaande join zal er zo uitzien:
Jansen -> Nl
Rubens -> De
Als je naar de tabellen kijkt zie je ook dat dit de enige coureurs zijn die bij een renstal zitten die een thuisbaan heeft. Door hier weer de verschillende JOIN typen op toe te passen, kan je ook gegevens over coureurs zonder thuisbaan ophalen.
Stel dat je de thuisbaan van een coureur wilt weten. Hiervoor hebben we de volgende tabellen nodig: (ik heb ze voor dit voorbeeld wat aangepast, omdat je anders met de datum rekening moet houden)
TABEL coureurs:
- id (1, 2, 3, 4, 5, 6)
- coureur (Jansen, Schumacher, Verstappen, Rubens, Heins, Markus)
- leeftijd (41, 39, 23, 34, 35, 24)
- renstal (1,NULL,NULL,3,4,NULL)
TABEL renstal:
- id (1,2,3,4)
- naam (Snel, Sneller, Snelst, Allersnelst)
TABEL racebaan:
- id (1,2,3,4,5)
- land (Nl, De, GB, Bra, Be)
- thuisbaan_van (1,3,NULL,NULL,2)
Hier zijn 3 tabellen voor nodig, zoals je ziet. Om de werking van het gebruik van meerdere JOINS in 1 query goed te kunnen begrijpen, moet je onthouden dat een JOIN 2 tabellen samenvoegt tot 1 nieuwe tabel.
<?php
$query= "SELECT
c.naam,
rb.land
FROM
coureurs AS c
INNER JOIN
(racebaan AS rb
INNER JOIN
renstal AS rs
ON
rs.id = rb.thuisbaan_van)
ON
rs.id =c.renstal";
?>
Je mag in deze voorbeeldquery stellen dat dit deel:
(racebaan AS rb
INNER JOIN
renstal AS rs
ON
rs.id = rb.thuisbaan_van)
Een nieuwe tabel vormt, uit die 2 tabellen, waar je vervolgens weer een join op kan toepassen. Op deze manier kan je je query uitbreiden tot een grote query met een groot aantal joins.
Het resultaat van bovenstaande join zal er zo uitzien:
Jansen -> Nl
Rubens -> De
Als je naar de tabellen kijkt zie je ook dat dit de enige coureurs zijn die bij een renstal zitten die een thuisbaan heeft. Door hier weer de verschillende JOIN typen op toe te passen, kan je ook gegevens over coureurs zonder thuisbaan ophalen.
Pagina 8
Tot Slot
Ik hoop dat ik jullie aan de hand van deze tutorial duidelijk(er) heb kunnen maken wat de SQL JOINS precies inhouden. Je hebt gezien dat de JOIN in sommige gevallen een prima oplossing bied om de juiste gegevens uit meerdere tabellen te halen.
Door het leren kennen van de JOIN heb je meer kennis van SQL, en zal waarschijnlijk weer meer SQL oplossingen gebruiken voor problemen die je anders met PHP en een 2e query op zou lossen. Natuurlijk zijn er wel altijd uitzonderingen, waarbij een JOIN geen uitkomst zal bieden, en waar je wel een tussenweg met PHP (of een andere scripttaal) zal moeten gebruiken.
Opmerkingen:
Normaliseer je database
Het is voor het goed functioneren van deze mogelijkheid in SQL heel belangrijk dat je database op een goede manier is opgebouwd. Normalisatie is voor het goed opbouwen van een database een goede methode. Daarom wil ik via hier nog even op deze tutorial wijzen: http://phphulp.nl/php/tutorials/3/150/
Andere mogelijkheden
Met de JOIN kan je de referentiële integriteit van de database gebruiken, om gekoppelde gegevens uit meerdere tabellen op de juiste manier op te halen. Echter is het geen manier om de referentiële integriteit te waarborgen.
Sommige providers bieden naast de standaard MySQL ook de mogelijkheid om PostgreSQL te gebruiken. Hierin zit een optie om sleutelvelden aan te geven en ook waaraan ze worden gekoppeld.
Wil je hier meer over weten kijk dan even bij deze 2 tutorials:
- http://www.phphulp.nl/php/tutorials/3/334/
- http://www.phphulp.nl/php/tutorials/3/371/
Rest van je query
De rest van de query kan je gewoon op de manier gebruiken zoals je bent gewend. Door een ORDER BY toe te voegen, of een WHERE clausule krijg je alleen de gegevens die je wilt hebben. Het maakt dus voor het vervolg van je query geen verschil of je een JOIN gebruikt of niet!
Door het leren kennen van de JOIN heb je meer kennis van SQL, en zal waarschijnlijk weer meer SQL oplossingen gebruiken voor problemen die je anders met PHP en een 2e query op zou lossen. Natuurlijk zijn er wel altijd uitzonderingen, waarbij een JOIN geen uitkomst zal bieden, en waar je wel een tussenweg met PHP (of een andere scripttaal) zal moeten gebruiken.
Opmerkingen:
Normaliseer je database
Het is voor het goed functioneren van deze mogelijkheid in SQL heel belangrijk dat je database op een goede manier is opgebouwd. Normalisatie is voor het goed opbouwen van een database een goede methode. Daarom wil ik via hier nog even op deze tutorial wijzen: http://phphulp.nl/php/tutorials/3/150/
Andere mogelijkheden
Met de JOIN kan je de referentiële integriteit van de database gebruiken, om gekoppelde gegevens uit meerdere tabellen op de juiste manier op te halen. Echter is het geen manier om de referentiële integriteit te waarborgen.
Sommige providers bieden naast de standaard MySQL ook de mogelijkheid om PostgreSQL te gebruiken. Hierin zit een optie om sleutelvelden aan te geven en ook waaraan ze worden gekoppeld.
Wil je hier meer over weten kijk dan even bij deze 2 tutorials:
- http://www.phphulp.nl/php/tutorials/3/334/
- http://www.phphulp.nl/php/tutorials/3/371/
Rest van je query
De rest van de query kan je gewoon op de manier gebruiken zoals je bent gewend. Door een ORDER BY toe te voegen, of een WHERE clausule krijg je alleen de gegevens die je wilt hebben. Het maakt dus voor het vervolg van je query geen verschil of je een JOIN gebruikt of niet!
Reacties
0