{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "(sqlite-2)=\n", "# Combineren van tabellen: join" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ ":::{admonition} Databases\n", "\n", "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.\n", ":::" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Als we een tabel willen maken met alle inschrijvingen voor een bepaalde event,\n", "met de namen van de leden erin,\n", "dan moeten we combinaties maken van de rijen van de leden-tabel en van de inschrijvingen-tabel.\n", "In een *cartesisch product* (zie opdrachten) maken we alle combinaties,\n", "maar we zijn hier alleen geïnteresseerd in de combinaties waarvoor geldt:\n", "``inschrijvingen.lidnr = leden.lidnr``.\n", "We krijgen dan onderstaande tabel:\n", "\n", ":::{figure} images/join-tabel.png\n", ":width: 600px\n", ":align: center\n", "\n", "Een join-tabel van ``leden`` en ``inschrijvingen`` waarvoor ``inschrijvingen.lidnr = leden.lidnr``\n", ":::\n", "\n", "Van dit resultaat kunnen we weer de kolommen selecteren (*projectie*)\n", "en de rijen (*selectie*) die we in het eindresultaat willen zien.\n", "\n", ":::{figure} images/join-tabel-selectie.png\n", ":width: 600px\n", ":align: center\n", "\n", "Een join-tabel na selectie en projectie\n", ":::" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Join in SQL" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "thebe-init", "hide-input" ] }, "outputs": [], "source": [ "%LOAD example.db" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "----" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cartesisch product\n", "\n", "Het cartesisch product van twee relaties (tabellen) bevat *alle combinaties* van de rijen van beide tabellen.\n", "\n", "* vraag: hoeveel rijen heeft een cartesisch product van twee tabellen met elk 1000 rijen? (1000; 2000; 1.000.000?)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
lidnrvoornaamachternaamemaileventnrlidnrmaaltijd
1Hansde Boerhdebo@ziggo.nl11maaltijd A
1Hansde Boerhdebo@ziggo.nl21maaltijd B
1Hansde Boerhdebo@ziggo.nl32geen
1Hansde Boerhdebo@ziggo.nl43maaltijd A
2MarieVerkerkmarie@verkerk.nl11maaltijd A
2MarieVerkerkmarie@verkerk.nl21maaltijd B
2MarieVerkerkmarie@verkerk.nl32geen
2MarieVerkerkmarie@verkerk.nl43maaltijd A
3JantienGerardsjgerards@gmail.com11maaltijd A
3JantienGerardsjgerards@gmail.com21maaltijd B
3JantienGerardsjgerards@gmail.com32geen
3JantienGerardsjgerards@gmail.com43maaltijd A
4MarieZandstramarie123@ziggo.nl11maaltijd A
4MarieZandstramarie123@ziggo.nl21maaltijd B
4MarieZandstramarie123@ziggo.nl32geen
4MarieZandstramarie123@ziggo.nl43maaltijd A
5EricaBezoserica@amazon.com11maaltijd A
5EricaBezoserica@amazon.com21maaltijd B
5EricaBezoserica@amazon.com32geen
5EricaBezoserica@amazon.com43maaltijd A
" ], "text/plain": [ "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| lidnr | voornaam | achternaam | email | eventnr | lidnr | maaltijd |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 4 | Marie | Zandstra | marie123@ziggo.nl | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 4 | Marie | Zandstra | marie123@ziggo.nl | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 4 | Marie | Zandstra | marie123@ziggo.nl | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 4 | Marie | Zandstra | marie123@ziggo.nl | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 5 | Erica | Bezos | erica@amazon.com | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 5 | Erica | Bezos | erica@amazon.com | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 5 | Erica | Bezos | erica@amazon.com | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 5 | Erica | Bezos | erica@amazon.com | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", "FROM leden, inschrijvingen;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Join\n", "\n", "De meeste van deze combinaties zijn zinloos, zoals de inschrijvingen met `lidnr 2`, in combinatie met de lid-gegevens van `lidnr 1`.\n", "We zijn alleen geïnteresseerd in de rijen waarvan de lidnr's gelijk zijn.\n", "We selecteren de relevante rijen van het cartesisch product door middel van een `WHERE`-voorwaarde.\n", "Dit is een normaal patroon voor een *join*." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
lidnrvoornaamachternaamemaileventnrlidnrmaaltijd
1Hansde Boerhdebo@ziggo.nl11maaltijd A
1Hansde Boerhdebo@ziggo.nl21maaltijd B
2MarieVerkerkmarie@verkerk.nl32geen
3JantienGerardsjgerards@gmail.com43maaltijd A
" ], "text/plain": [ "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| lidnr | voornaam | achternaam | email | eventnr | lidnr | maaltijd |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 1 | 1 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 2 | 1 | maaltijd B |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 3 | 2 | geen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 4 | 3 | maaltijd A |\n", "+-------+----------+------------+--------------------+---------+-------+------------+" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", "FROM leden lid, inschrijvingen ins\n", "WHERE lid.lidnr = ins.lidnr;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We kunnen bovenstaande combinatie (join) van tabellen combineren met andere selectievoorwaarden en met projectie, zoals hieronder:\n" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
voornaamachternaamemaileventnrmaaltijd
Hansde Boerhdebo@ziggo.nl1maaltijd A
Hansde Boerhdebo@ziggo.nl2maaltijd B
" ], "text/plain": [ "+----------+------------+----------------+---------+------------+\n", "| voornaam | achternaam | email | eventnr | maaltijd |\n", "+----------+------------+----------------+---------+------------+\n", "| Hans | de Boer | hdebo@ziggo.nl | 1 | maaltijd A |\n", "+----------+------------+----------------+---------+------------+\n", "| Hans | de Boer | hdebo@ziggo.nl | 2 | maaltijd B |\n", "+----------+------------+----------------+---------+------------+" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT lid.voornaam\n", ", lid.achternaam\n", ", lid.email\n", ", ins.eventnr\n", ", ins.maaltijd\n", "FROM leden lid, inschrijvingen ins\n", "WHERE lid.lidnr = ins.lidnr AND lid.voornaam = 'Hans';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ ":::{exercise} Cartesisch product\n", "\n", "Maak een query voor alle combinaties van de rijen van leden, inschrijvingen en events (Cartesisch product).\n", "Hoeveel rijen verwacht je in het resultaat?\n", ":::" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ ":::{exercise} Alle inschrijvingen met volledige gegevens\n", "\n", "Maak een query voor alle inschrijvingen, met daarbij alle gegevens van de leden en van de events.\n", ":::" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### INNER JOIN\n", "\n", "Het cartesisch product heet in SQL de `INNER JOIN`.\n", "Het resultaat van deze join is een tabel: deze staat in de query bij het `FROM`-deel.\n", "De voorwaarde (meestal: gelijke sleutels) staat bij de join als `ON`-voorwaarde.\n", "Op deze manier zijn de join-voorwaarde en de selectie-voorwaarde duidelijk gescheiden:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
voornaamachternaamemaileventnrmaaltijd
Hansde Boerhdebo@ziggo.nl1maaltijd A
Hansde Boerhdebo@ziggo.nl2maaltijd B
" ], "text/plain": [ "+----------+------------+----------------+---------+------------+\n", "| voornaam | achternaam | email | eventnr | maaltijd |\n", "+----------+------------+----------------+---------+------------+\n", "| Hans | de Boer | hdebo@ziggo.nl | 1 | maaltijd A |\n", "+----------+------------+----------------+---------+------------+\n", "| Hans | de Boer | hdebo@ziggo.nl | 2 | maaltijd B |\n", "+----------+------------+----------------+---------+------------+" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT lid.voornaam\n", ", lid.achternaam\n", ", lid.email\n", ", ins.eventnr\n", ", ins.maaltijd\n", "FROM leden lid \n", " JOIN inschrijvingen ins\n", " ON lid.lidnr = ins.lidnr \n", "WHERE lid.voornaam = 'Hans'; " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Meer dan 2 tabellen\n", "\n", "We kunnen een join ook over meer dan twee tabellen uitvoeren.\n", "Bij elke tabel geven we dan de join-conditie aan." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## (De)normalisatie\n", "\n", "De tabellen die we hierboven gebruiken zijn in *normaalvorm*:\n", "Elk basisgegeven komt maar één keer voor.\n", "Deze tabellen bevatten geen redundante (overtollige) gegevens.\n", "Dat maakt het eenvoudiger om de database te veranderen op een consistende manier.\n", "\n", "De volgende tabel (als resultaat van een \"join\" bewerking) is niet genormaliseerd:\n", "je ziet dat dezelfde voornaam, achternaam, email-adres en event-gegevens meerdere malen voorkomen.\n", "Dit is een manier van werken die je veel tegenkomt in relationele databases:\n", "\n", "* basisgegevens breng je onder in genormaliseerde tabellen; veranderingen in de basisgegevens hoef je dan maar op één plek in te voeren;\n", "* niet-genormaliseerde tabellen, bijvoorbeeld voor rapportages, reken je uit als dat nodig is." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
lidnrvoornaamachternaamemaileventnrlidnrmaaltijdeventnrdatumbeschrijving
1Hansde Boerhdebo@ziggo.nl11maaltijd A12019-08-21Schaaktoernooi Breda
1Hansde Boerhdebo@ziggo.nl21maaltijd B22019-08-28Schaaktoernooi Breda
2MarieVerkerkmarie@verkerk.nl32geen32019-08-28Schaaktoernooi Assen
3JantienGerardsjgerards@gmail.com43maaltijd A42019-09-15Schaaktoernooi Amersfoort
" ], "text/plain": [ "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+\n", "| lidnr | voornaam | achternaam | email | eventnr | lidnr | maaltijd | eventnr | datum | beschrijving |\n", "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 1 | 1 | maaltijd A | 1 | 2019-08-21 | Schaaktoernooi Breda |\n", "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+\n", "| 1 | Hans | de Boer | hdebo@ziggo.nl | 2 | 1 | maaltijd B | 2 | 2019-08-28 | Schaaktoernooi Breda |\n", "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+\n", "| 2 | Marie | Verkerk | marie@verkerk.nl | 3 | 2 | geen | 3 | 2019-08-28 | Schaaktoernooi Assen |\n", "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+\n", "| 3 | Jantien | Gerards | jgerards@gmail.com | 4 | 3 | maaltijd A | 4 | 2019-09-15 | Schaaktoernooi Amersfoort |\n", "+-------+----------+------------+--------------------+---------+-------+------------+---------+------------+---------------------------+" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", "FROM leden lid, inschrijvingen ins, events evt\n", "WHERE lid.lidnr = ins.lidnr AND evt.eventnr = ins.eventnr;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Meestal gebruik je maar een deel van de kolommen, zoals in de onderstaande projectie:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
voornaamachternaambeschrijvingdatummaaltijd
Hansde BoerSchaaktoernooi Breda2019-08-21maaltijd A
Hansde BoerSchaaktoernooi Breda2019-08-28maaltijd B
MarieVerkerkSchaaktoernooi Assen2019-08-28geen
JantienGerardsSchaaktoernooi Amersfoort2019-09-15maaltijd A
" ], "text/plain": [ "+----------+------------+---------------------------+------------+------------+\n", "| voornaam | achternaam | beschrijving | datum | maaltijd |\n", "+----------+------------+---------------------------+------------+------------+\n", "| Hans | de Boer | Schaaktoernooi Breda | 2019-08-21 | maaltijd A |\n", "+----------+------------+---------------------------+------------+------------+\n", "| Hans | de Boer | Schaaktoernooi Breda | 2019-08-28 | maaltijd B |\n", "+----------+------------+---------------------------+------------+------------+\n", "| Marie | Verkerk | Schaaktoernooi Assen | 2019-08-28 | geen |\n", "+----------+------------+---------------------------+------------+------------+\n", "| Jantien | Gerards | Schaaktoernooi Amersfoort | 2019-09-15 | maaltijd A |\n", "+----------+------------+---------------------------+------------+------------+" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT lid.voornaam\n", ", lid.achternaam\n", ", evt.beschrijving\n", ", evt.datum\n", ", ins.maaltijd\n", "FROM leden lid, inschrijvingen ins, events evt\n", "WHERE lid.lidnr = ins.lidnr AND evt.eventnr = ins.eventnr;" ] } ], "metadata": { "kernelspec": { "display_name": "xsqlite", "language": "sqlite", "name": "xsqlite" }, "language_info": { "codemirror_mode": "sql", "file_extension": "", "mimetype": "", "name": "sqlite3", "version": "3.33.0" } }, "nbformat": 4, "nbformat_minor": 4 }