In dit deel leer je data over verschillende tabellen heen te combineren. Er is nog veel meer te leren over de DML, maar dit is het meest gevorderde gebruik van de DML in deze cursus.

Om de integriteit van de database te bewaren, normaliseren we de gegevens in de database. Door normalisering geraakt de informatie echter verspreid over meer dan één tabel. De gebruiker heeft daar echter niet veel aan. Primaire en vreemde sleutels zeggen de gebruiker niets. Anders gesteld, een tabel vol getallen zoals onderaan op de pagina over primary keys is op zich niet leesbaar. We moeten dus een manier vinden om de informatie weer voor te stellen alsof die uit één tabel komt.

CROSS JOIN

De "domste" manier om data uit meerdere tabellen te combineren tot data die uit één tabel lijkt te komen, is de "cross join". Deze "plakt" elke rij uit tabel A aan elke rij uit tabel B. Veronderstel bijvoorbeeld volgende tabellen voor taken en voor personen:

Deze kunnen we op deze manier combineren en tonen:

SELECT *
FROM Taken
CROSS JOIN Leden
ORDER BY Taken.Id, Leden.Id;

Dit levert dan een resultaat dat er als volgt uitziet:

Dit bevat nuttige informatie, maar ook rijen waar we niets aan hebben. De interessante rijen zijn die, die een persoon koppelen aan een taak. Dat zijn de rijen waarin Taken.Id gelijk is aan Taken_Id (afkomstig uit Leden).

Je kan dus personen koppelen aan hun taak via:

SELECT *
FROM Taken
CROSS JOIN Leden
WHERE Taken.Id = Taken_Id;

Hier moet je Taken.Id schrijven omdat zowel Taken als Leden een kolom Id hebben. Door de tabelnaam toe te voegen, maak je duidelijk over welke kolom het precies gaat.

INNER JOIN

Dit laatste voorbeeld werkt in MySQL, maar het wordt typisch anders geschreven. Meestal zal CROSS JOIN vervangen worden door INNER JOIN, terwijl WHERE vervangen wordt door ON. Wanneer we twee tabellen willen koppelen zodat samenhorende rijen uit tabel A en tabel B één nieuwe rij opleveren, zullen we deze conventie volgen.

Het resultaat zal er dus zo uitzien:

SELECT *
FROM Taken
INNER JOIN Leden
ON Taken.Id = Taken_Id;

Het resultaat is hetzelfde, maar in dit scenario wordt INNER JOIN verkozen. ON is ook geen synoniem voor WHERE, want het kan alleen gebruikt worden in een JOIN-statement.

We weten wel hoe we leden aan taken kunnen koppelen door middel van INNER JOIN, maar die aanpak werkt niet wanneer er meer dan twee tabellen nodig zijn om de data terug te combineren, zoals in het geval van releases van games op platformen.

Om entiteiten gekoppeld via een M-op-N-relatie aan elkaar te koppelen, moet je eerst de informatie langs de M-kant koppelen met de tabel die de relatie voorstelt en vervolgens de informatie langs de N-kant koppelen.

Onze tabel met info over releases van games was:

We kunnen deze tonen met informatie over de game als volgt:

SELECT Games_Id, Platformen_Id, Titel
FROM Releases
INNER JOIN Games
ON Games_Id = Games.Id;

Dit voegt langs de rechterkant gewoon de details over de uitgebrachte game. Het is dus een meer uitgebreide versie van de tabel Releases. Daarom kunnen we dezelfde techniek herhalen en enkel de interessante info tonen:

SELECT Titel, Naam
FROM Releases
INNER JOIN Games
ON Games_Id = Games.Id
INNER JOIN Platformen
ON Platformen_Id = Platformen.Id;

JOIN is niet alleen handig om informatie samen te tonen. Deze operatie kan ook gebruikt worden om aanpassingen door te voeren waarvoor we informatie uit een andere tabel nodig hebben.

Volgend script werd in de oefeningen gebruikt om nummers via een ID te linken aan een artiest, nadat artiesten in een aparte tabel waren geplaatst. De liedjes hebben dus nog géén ID, maar bevatten wel nog de naam van de artiest:

use ApDB;
set sql_safe_updates = 0;
-- "Nummers" betekent hier "Liedjes"
update Nummers inner join Artiesten
on Artiesten.Naam = Nummers.Artiest
set Nummers.Artiesten_Id = Artiesten.Id;
set sql_safe_updates = 1;

Eerst moet je dus kijken naar wat een gewone SELECT van deze samengevoegde tabel zou opleveren. Hij zou informatie over een nummer plaatsen naast informatie over een artiest met dezelfde naam.

Voor uitvoering zien rijen uit de tabel met liedjes er dus zou uit:

Rijen uit de tabel met artiesten zien er zo uit:

Een combinatie van de twee tabellen kan je bekijken via (dus zoals in de update hierboven, maar de informatie wordt alleen getoond):

select * from Nummers inner join Artiesten
on Artiesten.Naam = Nummers.Artiest; -- niet op ID!

Deze ziet er zo uit:

Dus de kolom Artiesten_Id is nog leeg, maar we kunnen het volgende zeggen:

• ze is afkomstig uit Nummers

• de waarde die we wensen in te vullen is wel zichtbaar in deze JOIN, want ze staat in de kolom Id afkomstig uit Artiesten

We weten dus wat we zouden moeten invullen voor al deze NULL-waarden en we weten waar die info dan zou moeten worden opgeslagen. De UPDATE is dan ook toegelaten en werkt zoals gewenst.