Programming, it turns out, is hard. The fundamental rules are typically simple and clear. But programs built on top of these rules tend to become complex enough to introduce their own rules and complexity. You’re building your own maze, in a way, and you might just get lost in it.
—Marijn Haverbeke

Subquery's

Schema studenten_campus

Het gaat concreet om een opsomming van alle acht campussen van UCLL en een tabel met 1000 studenten met per student enkele persoonlijke gegevens, naar welke campus ze gaan (een student kan in dit model maar tot één campus behoren) en hoe ver ze wonen van deze campus. Dat laatste veld is niet verplicht. We maakten dit schema trouwens als voorbeeld voor automatische datageneratie in DBSchema of met behulp van een een site zoals https://mockaroo.com.

Wie woont verder van een campus dan de gemiddelde afstand?

Er zijn meerdere soorten SQL-vragen. Eén soort vraagt enkel het antwoord te geven op een vraag. Dat kan een getal zijn, één of meerdere namen, een datum, ... Voor dit type vraag hoef je de gebruikte query(s) niet te geven. Enkel het antwoord is voldoende. Een voorbeeld van een dergelijke vraag is de volgende oefening. Maak die en controleer je antwoord.

SELECT AVG(afstand_tot_campus)
FROM student;

SELECT count(*). -- of eventueel gewoon * en dan aantal rijen aflezen in de query tool
FROM student
WHERE afstand_tot_campus > 52.01658;

Voor dit type van SQLvraag antwoord je dus gewoon ‘401’. Prima, punt verdiend!

Voor een ander type examenvraag moet je echter de query geven. Bovendien mag je maar één query geven, die je lector moet kunnen kopiëren en uitvoeren in pgAdmin. Een oplossing met twee aparte query’s, waarbij je dan het antwoord van de eerste moet kopiëren naar de tweede query is niet toegelaten.

Bovenstaande oefening heeft een elegante oplossing: een subquery gebruiken (ook wel ‘geneste query’ genoemd). Volgende query geeft de oplossing (401) van de oefening in één query:

SELECT COUNT(*)
FROM student
WHERE afstand_tot_campus > (
  SELECT AVG(afstand_tot_campus)
  FROM student
);

In de WHERE clause wordt er een nieuwe SELECT gestart. Deze query noemen we de binnenquery (‘inner query’). De query errond wordt dan de buitenquery (‘outer query’) genoemd. Je kan verschillende niveau’s diep nesten. Een subquery kan één of meerdere rijen teruggeven aan de oproepende query. Het bovenstaande is een voorbeeld van een scalaire subquery: de subquery geeft immers enkel één getal terug. Je kan een subquery nesten in de SELECT, FROM, WHERE of HAVING clause.

Gemiddelde afstand van Proximus-studenten

Twee versies van dezelfde oefening

Los nu de volgende oefening op met een subquery:

SELECT avg(afstand_tot_campus)
FROM student
WHERE campus_id = (
  SELECT id
  FROM campus
  WHERE naam = 'Campus Proximus'
);

Misschien dacht je bij het lezen van bovenstaande opgave ‘dat kan ik toch ook gewoon met een JOIN?’. Terecht! Los nu dezelfde oefening op zonder subquery door enkel een JOIN te gebruiken:

SELECT avg(afstand_tot_campus)
FROM student S INNER JOIN campus C on S.campus_id = C.id
WHERE C.naam = 'Campus Proximus'

Kiezen tussen JOIN en subquery

Soms heb je geen keuze en moet je een subquery gebruiken, zoals bij de eerste oefening. Die kan je niet oplossen met een JOIN. Omgekeerd heb je voor sommige vragen een JOIN nodig. Vaak heb je echter de keuze tussen een subquery en een JOIN. Het is moeilijk om eenduidig te omschrijven wat de beste keuze is. Voor complexe queries is een JOIN vaak efficiënter. Een subquery is meestal wel eenvoudiger leesbaar.

Je moet in elk geval beide types van query kunnen gebruiken. Het is mogelijk dat je bij een bepaalde vraag op het opdracht expliciet één van beide zal moeten gebruiken.

Subquery met IN operator

We bekeken de IN operator al in het hoofdstuk over de WHERE clause. Achter de IN kan het resultaat van een subquery komen. Maak nu de volgende oefening.

Toon de vijf oudste studenten die op een campus in Heverlee zitten. Werk met een subquery.

Toon / verberg oplossing

SELECT voornaam || ' ' || familienaam AS naam, geboortedatum
FROM student
WHERE campus_id IN (
  SELECT id
  FROM campus
  WHERE locatie = 'Heverlee'
)
ORDER BY geboortedatum
LIMIT 5

SELECT voornaam || ' ' || familienaam AS naam, geboortedatum
FROM student S INNER JOIN campus C ON S.campus_id = C.id
WHERE locatie = 'Heverlee'
ORDER BY geboortedatum
LIMIT 5

Subquery met de ALL operator

De ALL operator vergelijkt een waarde met elke waarde van de resultaattabel. We illustreren dit met volgende voorbeeld.

Maak een lijst van alle studenten die jonger zijn dan alle studenten van wie de voornaam begint met ‘Al’. Dit zou je opnieuw met twee query’s kunnen oplossen. Vraag eerst de geboortedata op van alle studenten met de gevraagde voornaam:

SELECT geboortedatum
FROM student
WHERE (voornaam like 'Al%')
ORDER BY geboortedatum DESC

Het blijkt dat de jongste van deze studenten geboren is op 27 februari 2004. Je kan nu in een tweede query alle studenten opvragen met een geboortedatum groter dan (want ze moeten jonger zijn!) deze datum:

SELECT *
FROM student
WHERE geboortedatum > '2004-02-27'
ORDER BY geboortedatum DESC

Je vindt uiteindelijk een lijst van 22 studenten.

Deze twee opeenvolgende query’s kan je combineren in één query door gebruik te maken van een subquery. Je vindt dezelfde 22 studenten:

SELECT *
FROM student
WHERE geboortedatum > ALL (
  SELECT geboortedatum
  FROM student
  WHERE voornaam LIKE 'Al%'
)
ORDER BY geboortedatum DESC

De volgende oefening ziet er op het eerste zicht niet zo moeilijk uit, maar er is een onverwacht probleem waar je misschien wel wat mee zal worstelen. Tips en oplossing vind je op de gebruikelijke plaats, maar probeer eerst zelf!

Geef een lijst van alle studenten die dichter wonen bij hun campus dan alle studenten die naar een campus in Diest gaan en waarvan de familienaam twee keer de letter ‘a’ bevat (hoofdletters mogen ook). Je mag maar één query gebruiken (die natuurlijk wel subquery’s mag bevatten). Rangschik volgens stijgend campus_id en dalende afstand.

Toon / verberg oplossing

Het is een goed idee om een dergelijke oefening eerst in stukjes op te lossen. Deelvraag 1: wat is het campus_id van de campus(sen) in Diest?

SELECT id
FROM campus
WHERE locatie = 'Diest'

Er blijkt maar één campus in Diest te zijn, nl. de campus met id = 2. Hiermee kunnen we nu deelvraag 2 beantwoorden: geef alle studenten uit deze campus die een familienaam met twee keer (hoofd- of kleine letter maakt niet uit) een ‘a’ hebben.

SELECT *
FROM student
WHERE lower(familienaam) LIKE '%a%a%' AND campus_id = 2
ORDER BY afstand_tot_campus ASC

Ga na dat het resultaat effectief alleen studenten bevat die naar campus 2 gaan en een familienaam met minstens 2 keer de letter ‘a’ hebben. De kleinste afstand blijkt 15 km te zijn. Tenslotte kunnen we het laatste deel van de vraag beantwoorden: alle studenten uit de student-tabel die minder dan 15 km van hun campus wonen:

SELECT *
FROM student
WHERE afstand_tot_campus < 15
ORDER BY campus_id ASC, afstand_tot_campus DESC

Dit blijkt een lijst van 106 studenten te zijn. Alles samenpuzzelen tot één grote query, met twee subquery's erin is nu niet meer zo moeilijk:

SELECT *
from student
WHERE afstand_tot_campus < ALL (
  SELECT afstand_tot_campus
  FROM student
  WHERE lower(familienaam) LIKE '%a%a%' AND campus_id IN (
    SELECT id
    FROM campus
    WHERE locatie = 'Diest'
  )
)
ORDER BY campus_id ASC, afstand_tot_campus DESC

Toch blijkt deze query niet het juiste resultaat te geven. Het resultaat bevat immer geen enkele rij! Wat is er aan de hand?

Het probleem zit in deelvraag 2. De lijst met alle studenten uit campus 4 met twee keer een ‘a’ in de familienaam bevat immers een aantal null waarden in de afstand. Elke vergelijking van een getal met een null geeft altijd een null, zoals deze query aantoont:

SELECT null > 999 -- resultaat is niet true of false maar null
SELECT null <= 999 -- resultaat is niet true of false maar null

Het gevolg is dat elke test WHERE afstand_tot_campus < ALL ... altijd zal mislukken en er dus geen enkele rij overblijft in het resultaat. Gelukkig is de oplossing niet zo moeilijk: vermijd in deelvraag 2 null waarden in het resultaat . We voegen dus één enkele voorwaarde in de eerste subquery bij:

SELECT *
from student
WHERE afstand_tot_campus < ALL (
  SELECT afstand_tot_campus
  FROM student
  WHERE lower(familienaam) LIKE '%a%a%'
    AND afstand_tot_campus IS NOT null --Vermijd null in het deelresultaat
    AND campus_id IN (
      SELECT id
      FROM campus
      WHERE locatie = 'Diest'
    )
)
ORDER BY campus_id ASC, afstand_tot_campus DESC

Subquery met de ANY operator

De ANY operator vergelijkt een waarde met elke waarde in een tabel en is voldaan als de vergelijking minstens voor één waarde uit de tabel geldt. De tabel waarmee vergeleken wordt, moet minstens één waarde bevatten. Het voorbeeld hieronder legt het principe uit.

We willen een lijst van alle studenten van campus 7 die ouder zijn dan minstens één student van campus 4. Volgende code geeft een lijst van 135 studenten die voldoen aan de voorwaarde:

SELECT *
FROM student
WHERE campus_id = 7 AND geboortedatum < ANY(
  SELECT geboortedatum
  FROM student
  WHERE campus_id = 4
);

Zoals altijd is het een goed idee om een query goed te ontleden om te snappen hoe alles werkt. Laten we even de lijst opvragen van alle studenten van campus 7:

SELECT *
FROM student
WHERE campus_id = 7

Die lijst bevat 136 studenten, dus eentje meer dan de eerste query! We onderzoeken even snel waar het verschil zit.

We zoeken eerst de jongste student van campus 4:

SELECT *
FROM student
WHERE campus_id = 4
ORDER BY 4 DESC

Dat blijkt Lisa Vargas te zijn, geboren op 30 september 2004. In campus 7 is er echter één student die nog jonger is dan Lisa Vargas, nl. Kate Gonzales, geboren op 12 oktober 2004. Zij voldoet dus niet aan de voorwaarde met de ANY en staat bijgevolg niet in het resultaat.