1.3. Combineren van tabellen: join#

Databases

De SQL-pagina is een onderdeel van het Database-materiaal. Ook in dit geval maak je de pagina interactief via de “Live code” button, om te experimenteren met eigen varianten van de SQL-opdrachten.

Als we een tabel willen maken met alle inschrijvingen voor een bepaalde event, met de namen van de leden erin, dan moeten we combinaties maken van de rijen van de leden-tabel en van de inschrijvingen-tabel. In een cartesisch product (zie opdrachten) maken we alle combinaties, maar we zijn hier alleen geïnteresseerd in de combinaties waarvoor geldt: inschrijvingen.lidnr = leden.lidnr. We krijgen dan onderstaande tabel:

../_images/join-tabel.png

Fig. 1.1 Een join-tabel van leden en inschrijvingen waarvoor inschrijvingen.lidnr = leden.lidnr#

Van dit resultaat kunnen we weer de kolommen selecteren (projectie) en de rijen (selectie) die we in het eindresultaat willen zien.

../_images/join-tabel-selectie.png

Fig. 1.2 Een join-tabel na selectie en projectie#

1.3.1. Join in SQL#

%LOAD example.db

1.3.2. Cartesisch product#

Het cartesisch product van twee relaties (tabellen) bevat alle combinaties van de rijen van beide tabellen.

  • vraag: hoeveel rijen heeft een cartesisch product van twee tabellen met elk 1000 rijen? (1000; 2000; 1.000.000?)

SELECT *
FROM leden, inschrijvingen;
lidnr voornaam achternaam email eventnr lidnr maaltijd
1 Hans de Boer hdebo@ziggo.nl 1 1 maaltijd A
1 Hans de Boer hdebo@ziggo.nl 2 1 maaltijd B
1 Hans de Boer hdebo@ziggo.nl 3 2 geen
1 Hans de Boer hdebo@ziggo.nl 4 3 maaltijd A
2 Marie Verkerk marie@verkerk.nl 1 1 maaltijd A
2 Marie Verkerk marie@verkerk.nl 2 1 maaltijd B
2 Marie Verkerk marie@verkerk.nl 3 2 geen
2 Marie Verkerk marie@verkerk.nl 4 3 maaltijd A
3 Jantien Gerards jgerards@gmail.com 1 1 maaltijd A
3 Jantien Gerards jgerards@gmail.com 2 1 maaltijd B
3 Jantien Gerards jgerards@gmail.com 3 2 geen
3 Jantien Gerards jgerards@gmail.com 4 3 maaltijd A
4 Marie Zandstra marie123@ziggo.nl 1 1 maaltijd A
4 Marie Zandstra marie123@ziggo.nl 2 1 maaltijd B
4 Marie Zandstra marie123@ziggo.nl 3 2 geen
4 Marie Zandstra marie123@ziggo.nl 4 3 maaltijd A

1.3.3. Join#

De meeste van deze combinaties zijn zinloos, zoals de inschrijvingen met lidnr 2, in combinatie met de lid-gegevens van lidnr 1. We zijn alleen geïnteresseerd in de rijen waarvan de lidnr’s gelijk zijn. We selecteren de relevante rijen van het cartesisch product door middel van een WHERE-voorwaarde. Dit is een normaal patroon voor een join.

SELECT *
FROM leden lid, inschrijvingen ins
WHERE lid.lidnr = ins.lidnr;
lidnr voornaam achternaam email eventnr lidnr maaltijd
1 Hans de Boer hdebo@ziggo.nl 1 1 maaltijd A
1 Hans de Boer hdebo@ziggo.nl 2 1 maaltijd B
2 Marie Verkerk marie@verkerk.nl 3 2 geen
3 Jantien Gerards jgerards@gmail.com 4 3 maaltijd A

We kunnen bovenstaande combinatie (join) van tabellen combineren met andere selectievoorwaarden en met projectie, zoals hieronder:

SELECT lid.voornaam
,      lid.achternaam
,      lid.email
,      ins.eventnr
,      ins.maaltijd
FROM leden lid, inschrijvingen ins
WHERE lid.lidnr = ins.lidnr AND lid.voornaam = 'Hans';
voornaam achternaam email eventnr maaltijd
Hans de Boer hdebo@ziggo.nl 1 maaltijd A
Hans de Boer hdebo@ziggo.nl 2 maaltijd B

Opdracht 1.1 (Cartesisch product)

Maak een query voor alle combinaties van de rijen van leden, inschrijvingen en events (Cartesisch product). Hoeveel rijen verwacht je in het resultaat?

Opdracht 1.2 (Alle inschrijvingen met volledige gegevens)

Maak een query voor alle inschrijvingen, met daarbij alle gegevens van de leden en van de events.

1.3.3.1. INNER JOIN#

Het cartesisch product heet in SQL de INNER JOIN. Het resultaat van deze join is een tabel: deze staat in de query bij het FROM-deel. De voorwaarde (meestal: gelijke sleutels) staat bij de join als ON-voorwaarde. Op deze manier zijn de join-voorwaarde en de selectie-voorwaarde duidelijk gescheiden:

SELECT lid.voornaam
,      lid.achternaam
,      lid.email
,      ins.eventnr
,      ins.maaltijd
FROM leden lid 
     JOIN inschrijvingen ins
     ON lid.lidnr = ins.lidnr                  
WHERE lid.voornaam = 'Hans';
voornaam achternaam email eventnr maaltijd
Hans de Boer hdebo@ziggo.nl 1 maaltijd A
Hans de Boer hdebo@ziggo.nl 2 maaltijd B

1.3.3.2. Meer dan 2 tabellen#

We kunnen een join ook over meer dan twee tabellen uitvoeren. Bij elke tabel geven we dan de join-conditie aan.

1.3.4. (De)normalisatie#

De tabellen die we hierboven gebruiken zijn in normaalvorm: Elk basisgegeven komt maar één keer voor. Deze tabellen bevatten geen redundante (overtollige) gegevens. Dat maakt het eenvoudiger om de database te veranderen op een consistende manier.

De volgende tabel (als resultaat van een “join” bewerking) is niet genormaliseerd: je ziet dat dezelfde voornaam, achternaam, email-adres en event-gegevens meerdere malen voorkomen. Dit is een manier van werken die je veel tegenkomt in relationele databases:

  • basisgegevens breng je onder in genormaliseerde tabellen; veranderingen in de basisgegevens hoef je dan maar op één plek in te voeren;

  • niet-genormaliseerde tabellen, bijvoorbeeld voor rapportages, reken je uit als dat nodig is.

SELECT *
FROM leden lid, inschrijvingen ins, events evt
WHERE lid.lidnr = ins.lidnr AND evt.eventnr = ins.eventnr;
lidnr voornaam achternaam email eventnr lidnr maaltijd eventnr datum beschrijving
1 Hans de Boer hdebo@ziggo.nl 1 1 maaltijd A 1 2019-08-21 Schaaktoernooi Breda
1 Hans de Boer hdebo@ziggo.nl 2 1 maaltijd B 2 2019-08-28 Schaaktoernooi Breda
2 Marie Verkerk marie@verkerk.nl 3 2 geen 3 2019-08-28 Schaaktoernooi Assen
3 Jantien Gerards jgerards@gmail.com 4 3 maaltijd A 4 2019-09-15 Schaaktoernooi Amersfoort

Meestal gebruik je maar een deel van de kolommen, zoals in de onderstaande projectie:

SELECT lid.voornaam
,      lid.achternaam
,      evt.beschrijving
,      evt.datum
,      ins.maaltijd
FROM leden lid, inschrijvingen ins, events evt
WHERE lid.lidnr = ins.lidnr AND evt.eventnr = ins.eventnr;
voornaam achternaam beschrijving datum maaltijd
Hans de Boer Schaaktoernooi Breda 2019-08-21 maaltijd A
Hans de Boer Schaaktoernooi Breda 2019-08-28 maaltijd B
Marie Verkerk Schaaktoernooi Assen 2019-08-28 geen
Jantien Gerards Schaaktoernooi Amersfoort 2019-09-15 maaltijd A