Nederlands
Menu

Inner join versus outer join: behouden rijen, NULL's, left en right outer joins en SQL-syntaxis

Een join combineert rijen uit twee tabellen onder de voorwaarde in ON (meestal dat een vreemde sleutel gelijk is aan een primaire sleutel) tot één resultaattabel. Een inner join behoudt alleen de combinaties van rijen die een passende partner hebben; rijen zonder partner verdwijnen uit beide tabellen. Een outer join behoudt de passende combinaties en daarnaast de rijen van de opgegeven tabel, ook als ze geen partner hebben, en vult de kolommen van de andere tabel met NULL. Een left outer join (LEFT OUTER JOIN) behoudt elke rij van de linkertabel, een right outer join (RIGHT OUTER JOIN) die van de rechtertabel en een full outer join (FULL OUTER JOIN) die van beide. Kies op basis van de vraag of rijen zonder partner in het resultaat moeten verschijnen.

KenmerkInner join (INNER JOIN)Outer join (OUTER JOIN)
Rijen in het resultaatAlleen combinaties van rijen die aan de joinvoorwaarde voldoenPassende combinaties, plus rijen zonder passende partner uit de tabel die behouden moet blijven
Rijen zonder partnerUit het resultaat verwijderd, uit beide tabellenRijen van de behouden tabel blijven elk als één rij staan, met de kolommen van de andere tabel op NULL; rijen zonder partner uit de andere tabel worden verwijderd
SoortenEén soort; INNER kan worden weggelaten en dan schrijf je gewoon JOINDrie soorten: left (LEFT), right (RIGHT) en full (FULL); OUTER kan worden weggelaten
Voorbeeld in SQLSELECT … FROM employee INNER JOIN dept ON employee.dept_id = dept.dept_idSELECT … FROM employee LEFT OUTER JOIN dept ON employee.dept_id = dept.dept_id
Voorbeeld van het aantal rijen (4 medewerkers, van wie één met afdelings-ID NULL; 3 afdelingen, waarvan één zonder medewerkers)3 rijen: de medewerker met afdelings-ID NULL en de afdeling zonder medewerkers verschijnen niet4 rijen met een left outer join die de medewerkerstabel behoudt (de medewerker met afdelings-ID NULL krijgt een afdelingsnaam NULL); 5 rijen met een full outer join die beide behoudt
Volgorde van de tabellenLinks en rechts verwisselen geeft dezelfde rijen (alleen de volgorde van de kolommen verandert)Bij left en right outer joins verandert het resultaat met de tabel die links staat; A LEFT OUTER JOIN B geeft dezelfde rijen als B RIGHT OUTER JOIN A
Voorwaarden op kolommen van de andere tabelHetzelfde resultaat, of ze nu in ON of in de WHERE-clausule staanIn de WHERE-clausule voldoen de met NULL gevulde rijen niet aan de voorwaarde en verdwijnen ze, wat hetzelfde resultaat kan geven als een inner join; zet de voorwaarde in ON om rijen zonder partner te behouden
Typische toepassingenWanneer alleen data die in beide tabellen voorkomt gewenst is (bijvoorbeeld klantnamen aan bestellingen toevoegen)Wanneer ook rijen zonder partner moeten worden opgesomd (bijvoorbeeld alle medewerkers, ook die zonder afdeling), of om rijen zonder partner op te sporen

De getalswaarden zijn typische ordegroottes en variëren per procesgeneratie en product.

Wanneer welke variant?

Inner join
Gebruik hem wanneer alleen rijen met passende data in beide tabellen nodig zijn. Wil je bijvoorbeeld klantnamen aan een lijst met bestellingen toevoegen en zijn bestellingen met een onbekende klant niet nodig, gebruik dan een inner join.
Outer join
Gebruik hem wanneer rijen zonder partner niet mogen wegvallen. Wil je bijvoorbeeld afdelingsnamen toevoegen aan een lijst van alle medewerkers en daarbij medewerkers zonder afdeling behouden, gebruik dan een left outer join die de medewerkerstabel behoudt. Met een outer join kun je ook rijen zonder partner vinden, zoals klanten die nooit iets hebben besteld, door de rijen te selecteren waarin de primaire-sleutelkolom van de andere tabel NULL is.

Veelgestelde vragen

Hoe verschillen left en right outer joins?

Alleen in welke tabel al haar rijen behoudt. Een left outer join behoudt de rijen van de linkertabel, die als eerste in FROM staat, ook als ze geen partner hebben; een right outer join behoudt de rijen van de rechtertabel, die als tweede staat. A LEFT OUTER JOIN B en B RIGHT OUTER JOIN A geven dezelfde rijen, dus voor de leesbaarheid worden query's vaak consequent met left outer joins geschreven.

Waarom kan een join meer rijen opleveren dan de oorspronkelijke tabellen?

Bij een join wordt elke combinatie van rijen die aan de voorwaarde voldoet één resultaatrij. Als de joinkolom in beide tabellen dubbele waarden heeft, vermenigvuldigt het aantal combinaties zich. Heeft de ene tabel bijvoorbeeld 2 rijen met de waarde 1 en de andere ook 2, dan levert alleen die waarde al 2 × 2 = 4 rijen op.

Wat gebeurt er zonder joinvoorwaarde?

Het resultaat is elke combinatie van rijen uit de twee tabellen (het cartesisch product). Dit heet een CROSS JOIN: een tabel van 4 rijen en een tabel van 3 rijen geven 4 × 3 = 12 rijen. Per ongeluk een cartesisch product maken, bijvoorbeeld door ON te vergeten, laat het aantal rijen exploderen, dus let op.

Komen NULL's in een joinvoorwaarde met elkaar overeen?

Nee. In SQL is een vergelijking met NULL nooit waar, dus rijen waarvan de joinkolommen in beide tabellen NULL zijn, worden niet gecombineerd. Daardoor laat een inner join rijen vallen waarvan de joinkolom NULL is.

Test uw begrip

De tabel Medewerker (MedewerkerNr, Naam, AfdelingID) heeft vier rijen met als waarden voor AfdelingID achtereenvolgens 10, 20, 10 en NULL. De tabel Afdeling (AfdelingID, Afdelingsnaam) heeft drie rijen met AfdelingID 10, 20 en 30. Hoeveel rijen geeft SELECT Medewerker.Naam, Afdeling.Afdelingsnaam FROM Medewerker INNER JOIN Afdeling ON Medewerker.AfdelingID = Afdeling.AfdelingID?

  1. A3 rijen
  2. B4 rijen
  3. C12 rijen
Antwoord en toelichting tonen

Antwoord: A. 3 rijen

Een INNER JOIN geeft alleen de combinaties van rijen terug die aan de ON-voorwaarde voldoen. De twee medewerkers met AfdelingID 10 worden gecombineerd met afdeling 10, en de ene medewerker met AfdelingID 20 met afdeling 20, dus het resultaat heeft drie rijen. De medewerker met AfdelingID NULL wordt aan geen enkele afdeling gekoppeld, omdat een vergelijking met NULL nooit waar is, en verdwijnt uit het resultaat. Ook afdeling 30, die geen medewerkers heeft, verschijnt niet. Vier rijen is het aantal bij een left outer join die elke rij van de tabel Medewerker behoudt (Medewerker LEFT OUTER JOIN Afdeling). Twaalf rijen is de CROSS JOIN (het cartesisch product), die zonder voorwaarde elke combinatie vormt: 4 × 3 = 12 rijen.

De tabel Medewerker (MedewerkerNr, Naam, AfdelingID) heeft vijf rijen met als waarden voor AfdelingID achtereenvolgens 10, 20, 10, NULL en NULL. De tabel Afdeling (AfdelingID, Afdelingsnaam) heeft drie rijen met AfdelingID 10, 20 en 30. Hoeveel rijen geeft SELECT Afdeling.Afdelingsnaam, Medewerker.Naam FROM Afdeling LEFT OUTER JOIN Medewerker ON Afdeling.AfdelingID = Medewerker.AfdelingID?

  1. A3 rijen
  2. B4 rijen
  3. C5 rijen
Antwoord en toelichting tonen

Antwoord: B. 4 rijen

Een LEFT OUTER JOIN behoudt elke rij van de linkertabel (hier de tabel Afdeling); als de rechtertabel geen passende rij heeft, levert ze één rij op waarin de kolommen van de rechtertabel met NULL zijn gevuld. Afdeling 10 heeft twee medewerkers en geeft twee rijen, afdeling 20 geeft één rij, en afdeling 30, die geen medewerkers heeft, geeft één rij met NULL als naam, samen vier rijen. De twee medewerkers met AfdelingID NULL passen bij geen enkele afdeling en verschijnen, als rijen van de rechtertabel die niet volledig wordt behouden, niet in het resultaat. Een inner join zou ook de rij van afdeling 30 laten vallen en drie rijen geven. Worden de tabellen omgewisseld tot Medewerker LEFT OUTER JOIN Afdeling, dan blijven alle vijf medewerkers behouden en zijn het vijf rijen. Bij outer joins is het belangrijk na te gaan van welke tabel alle rijen behouden blijven.

Kolom x van tabel R heeft vier rijen met de waarden 1, 1, 2, 3, en kolom x van tabel S heeft vier rijen met de waarden 1, 1, 2, 4. Hoeveel rijen geeft SELECT * FROM R INNER JOIN S ON R.x = S.x?

  1. A2 rijen
  2. B3 rijen
  3. C5 rijen
Antwoord en toelichting tonen

Antwoord: C. 5 rijen

Bij een join wordt elke combinatie van rijen die aan de voorwaarde voldoet één resultaatrij. Voor x = 1 voldoet elke combinatie van de twee rijen van R en de twee rijen van S aan de voorwaarde, wat 2 × 2 = 4 rijen geeft; voor x = 2 is er 1 × 1 = 1 rij. De 3 van R en de 4 van S hebben geen partner en verschijnen dus niet in de inner join. Het totaal is 4 + 1 = 5 rijen. Twee rijen is het aantal als per overeenkomende waarde (1 en 2) maar één rij zou ontstaan. Drie rijen is het aantal als voor elke rij van R alleen wordt nagegaan of S een overeenkomende waarde bevat (een query zoals SELECT * FROM R WHERE x IN (SELECT x FROM S)). Het cartesisch product zonder voorwaarde zou 4 × 4 = 16 rijen geven. Houd er rekening mee dat dubbele waarden in de joinkolom het aantal resultaatrijen vermenigvuldigen.

  1. Uit de tabel Klant (KlantID, Naam) en de tabel Bestelling (BestelID, KlantID, Bedrag) moeten de namen worden opgevraagd van klanten die nog nooit iets hebben besteld. Welk SQL-statement is juist?

Oefenvragen over Basis van databases

Belangrijke termen uitgelegd

SQL
De standaardtaal voor het definiëren van tabellen, het zoeken, toevoegen, wijzigen en verwijderen van gegevens en het regelen van rechten in relationele databases. Ze is gestandaardiseerd door ISO/IEC (in Japan ook als JIS). Tot haar statements behoren SELECT, INSERT, UPDATE, DELETE, CREATE TABLE en GRANT.
Relationele database
Database die gegevens voorstelt als tabellen (relaties) van rijen en kolommen en via gemeenschappelijke waarden verbanden tussen tabellen legt. Wordt ook RDB genoemd. Query's en wijzigingen worden in SQL uitgevoerd, en beperkingen zoals primaire sleutels en vreemde sleutels houden de gegevens consistent.
Primaire sleutel
Kolom of combinatie van kolommen waarmee één rij in een tabel uniek wordt geïdentificeerd. Er gelden de beperkingen dat de waarden niet dubbel mogen voorkomen en geen NULL mogen bevatten. De primaire sleutel wordt gekozen uit de minimale combinaties van kolommen die een rij uniek kunnen identificeren (kandidaatsleutels).
Vreemde sleutel (foreign key)
Kolom die verwijst naar de primaire sleutel (of een unieke kolom) van een andere tabel. Via de beperking van referentiële integriteit, die alleen waarden toestaat die in de tabel waarnaar wordt verwezen bestaan, of NULL, voorkomt ze gegevens die naar niet-bestaande rijen wijzen. Ook het gedrag bij het verwijderen of wijzigen van een rij waarnaar wordt verwezen (weigeren, verwijderen in cascade enzovoort) kan worden opgegeven.
Normalisatie
Ontwerpprocedure die tabellen van een relationele database opsplitst op basis van functionele afhankelijkheden tussen kolommen, zodat hetzelfde feit niet op meerdere plekken wordt vastgelegd. Ze verwijdert dubbele gegevens en voorkomt wijzigings-, invoeg- en verwijderanomalieën. De stappen omvatten de eerste tot en met de derde normaalvorm en de Boyce-Codd-normaalvorm.
View (databaseview)
Virtuele tabel in een relationele database die wordt gedefinieerd door een SELECT-statement een naam te geven. Ze bevat normaal geen eigen gegevens en bouwt haar resultaat telkens bij raadpleging op uit de onderliggende tabellen. Ze wordt gebruikt om complexe query's te vereenvoudigen en om de toegang te beperken door de getoonde kolommen en rijen te begrenzen.

Verwante vergelijkingen