You can’t complain about the sea if you suffer shipwreck for the second time.
—IJslands gezegde

Tabellen combineren met OUTER JOIN

Ook hier starten we met een filmpje, nl. het vervolg op het filmpje van het vorig hoofdstuk.

De nood aan een OUTER JOIN

Maak als inleiding volgende oefening.

Onze opleidingsverantwoordelijke wil een overzicht van alle lectoren met hun lectornummer, voornaam, naam en de naam van het OPO waarvan ze coördinator zijn. Sorteer op familienaam alfabetisch. Je moet de figuur hieronder bekomen.

Niet zo moeilijk: 

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM lector L INNER JOIN opleidingsonderdeel OPO ON L.lector_id = OPO.coordinator
ORDER BY 3

We overlopen nog eens de stappen die het RDBMS zet bij het uitvoeren van deze query:

  1. Als eerste wordt de FROM uitgevoerd. Het gaat hier om een INNER JOIN van twee tabellen en dus wordt eerst het cartesisch product gemaakt: elke rij van de lectortabel wordt gekoppeld aan elke rij van de OPOtabel.
  2. Niet alle combinaties in dit cartesisch product zijn zinvol: enkel die waar het lectornummer (in de ene tabel heet dat ‘lector_id’, in de andere ‘coordinator’) overeenkomt blijven behouden, want die voldoen aan de joinconditie ON L.lector_id = OPO.coordinator.
  3. Er is geen WHERE component, dus er vallen geen rijen meer weg uit het resultaat.
  4. Ook de GROUP BY (en HAVING) ontbreekt.
  5. Tenslotte worden de resultaatrijen alfabetisch gerangschikt op familienaam via de ORDER BY component.

Je trekt fier met de gevraagde lijst naar de opleidingsverantwoordelijke, die jammer genoeg niet helemaal tevreden is met het resultaat. “Ik wou eigenlijk dat alle lectoren in de lijst staan, ook zij die van geen enkel OPO coördinator zijn” hoor je hem zeggen. De INNER JOIN in je SQL-query toont echter alleen de lectoren die in beide tabellen voorkomen, dus enkel de coördinatoren van OPO’s.

Als je toch alle lectoren in de lijst wilt opnemen, moeten de ontbrekende lectoren toegevoegd worden aan het resultaat van de INNER JOIN. Dat is precies wat een OUTER JOIN doet. Je hoeft enkel het woord INNER te vervangen door LEFT OUTER of RIGHT OUTER (of eventueel FULL OUTER):

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM lector L LEFT OUTER JOIN opleidingsonderdeel OPO ON L.lector_id = OPO.coordinator
ORDER BY 3

Waarom LEFT? Deze join bevat twee tabellen: eentje links van het woord JOIN (nl. ‘lector’) en eentje rechts van het woord JOIN (nl. ‘opleidingsonderdeel’). Na de gewone INNER JOIN ontbreken nog enkele lectoren uit de lectortabel. Omdat die tabel links van het woord JOIN staat, wordt dit dus een LEFT OUTER JOIN.

Deze code is volledig evenwaardig met deze variant:

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM opleidingsonderdeel OPO RIGHT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
ORDER BY 3

We hebben immers gewoon de volgorde waarin de twee tabellen geschreven staan omgewisseld. Dat heeft geen enkele invloed op de INNER JOIN die eerst gebeurt, maar je moet dus wel het woord LEFT vervangen door RIGHT. De volgende figuur toont het resultaat. In vergelijking met de eerdere figuur zijn er een aantal rijen toegevoegd. Zo is er bvb. lector Elke Crabbé die van geen enkel OPO coördinator is. Aangezien zij niet in de tabel ‘opleidingsonderdeel’ voorkomt, kan er ook geen informatie over de naam van het OPO gegeven worden. Daarom staat er null (geen waarde) in de laatste kolom (oranje pijl).

Leg uit waarom volgende twee query’s exact hetzelfde resultaat geven: 

-- query 1
SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM opleidingsonderdeel OPO LEFT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
ORDER BY 3

-- query 2
SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM opleidingsonderdeel OPO INNER JOIN lector L ON L.lector_id = OPO.coordinator
ORDER BY 3

Van query 2 met de INNER JOIN ken je het resultaat al. Query 1 is een LEFT OUTER JOIN. Elke OUTER JOIN begint eerst als een gewone INNER JOIN . Als die gedaan is, wordt er gekeken welke rijen uit de linkertabel ontbreken. Die worden dan toegevoegd. Hier is de linkertabel echter de tabel ‘opleidingsonderdeel’. Elke rij van deze tabel is al vertegenwoordigd in de INNER JOIN (waarom?), dus er wordt geen enkele rij meer toegevoegd. Beide query's geven daarom hetzelfde resultaat.

JOIN en GROUP BY

JOIN is zonder probleem te combineren met andere componenten, dus ook met GROUP BY en HAVING.

We werken volgend voorbeeld uit. Probeer je alle stapjes zelf uit? Er zal een aantal keer iets fout lopen. Zoek altijd eerst zelf de fout.

Maak een lijst van alle docenten met het aantal OPO’s waarvoor ze coördinator zijn. Ook lectoren die van geen enkel OPO coördinator zijn, moeten in de lijst opgenomen worden. Sorteer de lijst alfabetisch op naam.

Analyseer de opgave:

Om te voorspellen wat we zouden moeten bekomen, vertrekken we van de bekende code die de figuur in de sectie hierboven genereerde:

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM opleidingsonderdeel OPO RIGHT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
ORDER BY 3

Bekijk de lijst in die figuur. Lector ‘Gerben Adriaens’ is van één OPO coördinator, ‘Goedele Bogers’ van twee, ‘Elke Crabbé’ geen enkel enz.

Pas deze code aan met een GROUP BY:

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM opleidingsonderdeel OPO RIGHT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
GROUP BY lector_id
ORDER BY 3

Je krijgt een foutmelding (denk even na waarom):

ERROR: column "opo.naam" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: SELECT lector_id, L.voornaam, L.naam, OPO.naam

Waarom komt er een foutmelding over ‘OPO.naam’? Als je het hoofdstuk over GROUP BY hebt verwerkt, zou je het antwoord op die vraag moeten kunnen geven. Door de informatie te groeperen in doosjes per lector, gaat de detailinformatie over de OPO’s verloren. Het enige wat je nog kan opvragen is informatie uit de zogenaamde ‘aggregatiefuncties’ MIN(), MAX(), AVG(), COUNT() en SUM().

Er is echter iets vreemd aan de hand. Waarom komt er geen foutmelding over L.voornaam en L.naam? We hadden toch uitgelegd dat je in de SELECT enkel de kolommen kan vermelden die in de GROUP BY staan (en ook aggregatiefuncties)? Waarom krijg je dan geen foutmelding voor L.voornaam en L.naam?

De reden is dat je hier groepeert op de primaire sleutel van de lectortabel. Je bent dus zeker dat er één doosje zal gemaakt worden per lector. In dat doosje zit maar één lectornaam, lectorvoornaam enz, maar wel meerdere OPO’s. Daarom kan je wel de kolommen L.naam en L.voornaam opvragen, maar niet OPO.naam.

Je moet dus in de code een aggregatiefunctie gebruiken. In dit geval wordt dat het tellen van een aantal keer dat iets voorkomt, dus een COUNT(). Pas de code aan tot:

SELECT lector_id, L.voornaam, L.naam, COUNT(*)
FROM opleidingsonderdeel OPO RIGHT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
GROUP BY lector_id
ORDER BY 3

Je bekomt dit (fout!) resultaat:

Er is een probleem met deze figuur (en dus met onze code). Vergelijk even met de vorige figuur. Gerben heeft wel degelijk 1 OPO, Goedele is coördinator van twee OPO’s, maar Elke Crabbé is van geen enkel OPO coördinator. Het resultaat van onze code is echter dat Elke Crabbé van één OPO coördinator is (zie oranje pijl in de figuur). Wat loopt er fout?

De fout zit in de de aggregatiefunctie COUNT(*). Deze functie (met *) telt alle rijen, ook die met één of meerdere NULL waarden. De rij met de info van Elke Crabbé wordt dus effectief geteld. Dit probleem is gelukkig eenvoudig op te lossen. In plaats van de * vermeld je de kolom die van belang is voor wat je wilt tellen. Hier gaat het dus concreet om ‘OPO.naam’. Een waarde NULL in dit veld zal niet meetellen. Je beëindigt dus de SELECT met COUNT(OPO.naam):

SELECT lector_id, L.voornaam, L.naam, COUNT(OPO.naam)
FROM opleidingsonderdeel OPO RIGHT OUTER JOIN lector L ON L.lector_id = OPO.coordinator
GROUP BY lector_id
ORDER BY 3

Rijen selecteren die NIET aan een bepaalde voorwaarde voldoen

Opmerking: dit is een behoorlijk moeilijk stuk. Oefeningen die op dit principe steunen, zijn best wel pittig!

Je krijgt een nieuwe vraag van de opleidingsverantwoordelijke: “Ik wil graag een lijst van alle lectoren die GEEN coördinator zijn van één of meerdere OPO’s.”.

Fluitje van een cent, natuurlijk! Met de OUTER JOIN van vorige sectie kreeg je een lijst van alle coördinatoren, aangevuld met de lectoren die niet in de OPOtabel voorkomen en dus geen coördinator zijn. Die ‘toegevoegde’ lectoren herken je aan het ontbreken van een waarde voor de kolom met de OPOnaam. Rijen filteren nadat de FROM uitgevoerd is, doe je met de WHERE component. De query wordt dus:

SELECT lector_id, L.voornaam, L.naam, OPO.naam
FROM lector L LEFT OUTER JOIN opleidingsonderdeel OPO ON L.lector_id = OPO.coordinator
WHERE naam = NULL -- principe is OK, code niet! Zie opdracht hieronder
ORDER BY 3

We hebben met opzet twee fouten in de WHERE component van bovenstaande query gestoken. Verbeter die zodat de query correct wordt uitgevoerd en je een lijst bekomt met vier lectoren: Elke, Maarten, Patrick en Lut.

Fout 1: ‘naam’ is dubbelzinnig. Gaat het om de naam van de lector of de naam van het OPO? Tweede fout: vergelijken met NULL mag nooit met ‘=’ maar moet met het woord ‘IS’. De correcte WHERE component is: WHERE OPO.naam IS NULL.

Oefening

Maak een lijst van alle lectoren die geen coördinator zijn van een OPO dat 6 SP telt. Orden alfabetisch op naam en daarna op voornaam. Je moet het resultaat uit deze figuur bekomen:

Tip: begin deze oefening met meer kolommen dan alleen voornaam en naam (mogelijk zelfs met alle kolommen!). Maak eerst een INNER JOIN. Bekijk het resultaat. Vorm je query nu om naar een OUTER JOIN en bekijk wat er toegevoegd is. 

SELECT l.naam, l.voornaam
FROM opleidingsonderdeel o RIGHT OUTER JOIN lector l ON
  o.coordinator = l.lector_id AND o.studiepunten = 6
WHERE o.studiepunten IS NULL
ORDER BY 1, 2

FULL OUTER JOIN

Hierboven werd uitgelegd dat een OUTER JOIN een INNER JOIN is waar alle ontbrekende elementen uit één tabel (die aan de LEFT of de RIGHT zijde) worden toegevoegd. Er bestaat ook een FULL OUTER JOIN wat in essentie het toevoegen betekent van de ontbrekende elementen uit zowel de linker- als de rechtertabel . In ons voorbeeld met slechts twee tabellen (lectoren en OPO’s) kunnen we echter geen zinvolle FULL OUTER JOIN maken. Dit komt later in de oefeningen aan bod. In het filmpje bij het begin van dit hoofdstuk wordt het wel goed uitgelegd.

Samenvattend overzicht JOIN

Hieronder vind je nog eens een overzicht van alle verschillende types JOIN die we gezien hebben. Per type wordt opgelijst hoe de set van rijen waarmee de rest van de query wordt uitgevoerd wordt opgebouwd.