Subqueries

Uit Yapf

Wat zijn subqueries?

Subqueries zijn queries binnen queries. Je kunt ze gebruiken om ingewikkelde joins te vereenvoudigen, of om queries samen te voegen.

Een voorbeeld.

Een simpele vorm is letterlijk een query in een query:

SELECT
  voornaam,
  (
   SELECT
     titel
   FROM
     jobdescription
   WHERE
     jobid=users.jobid
  )
FROM
  users;

Subqueries als tabellen.

SELECT FROM <subquery>

Omdat een subquery een ook hele records terug kan geven kun je ze ook gebruiken als alternatief voor een tabelnaam.

Dat betekent dat je het bekende probleem "ik wil de laatste tien records, maar met de oudste eerst" kunt oplossen door de laatste tien records te selecteren met:

SELECT
 *
FROM
 tabel
ORDER BY
 datum DESC
LIMIT 10;

En dat als subquery te gebruiken voor een ORDER BY query:

SELECT
 *
FROM
(
 SELECT
  *
 FROM
  tabel
 ORDER BY
  datum DESC
 LIMIT 10
)
ORDER BY
 datum ASC;

JOINen met subqueries

Dit is waar de pret echt begint; je kunt JOINs uitvoeren tussen subqueries.

Stel dat je een forum hebt en je wilt voor elk forum de nieuwste post ophalen, dan kunt je de nieuwste datum per forum ophalen met:

SELECT
  forumid,
  MAX(datum)
FROM
  forumposts
GROUP BY
  forumid;

Maar hoe krijg je hier de rest van de post bij? Met een subquery:

SELECT
  *
FROM
  forumposts
INNER JOIN
  (
   SELECT
     forumid,
     MAX(datum) AS laatste_datum
   FROM
     forumposts
   GROUP BY
    forumid) AS laatsteposts
ON
  forumposts.datum = laatsteposts.laatste_datum
AND
  forumposts.forumid = laatsteposts.forumid;

En voila.

Dit voorbeeld is overdreven, elke post heeft zijn eigen PK waarde (postid ofzo) maar dit voorbeeld laat goed zien dat je kunt joinen op meerdere kolommen uit de subquery.

Top N per groep

Een veelgestelde vraag is hoe krijg ik een lijst met maximaal drie bestellingen per klant

Een lijst van alle bestellingen is eenvoudig maar hoe kap je het af op drie? De voorwaarde is simpel; de eerste drie dat zijn alle bestellingen wiens id/datum kleiner is dan dat van de vierde bestelling. Hoe bepaal je wat de waarde van de vierde bestelling is? Daar komen de correlated subqueries weer om de hoek kijken.

SELECT
  *
FROM
  orders
WHERE
  orders.orderid < COALESCE(
    (SELECT
       o2.orderid
     FROM
       orders o2
     WHERE
       orders.customerid=o2.customerid ORDER BY o2.orderid
     LIMIT 1
     OFFSET 3), 999999999)

Deze query bevat in de WHERE clausule een correlated subquery die het id van de vierde bestelling ophaalt. Let op dat deze subquery ook het customerid bekijkt zodat hij voor elke customer apart ophaalt wat het vierde bestellingid is. Hij haalt niet zomaar "de vierde bestelling" op, maar de vierde bestelling van de customer waar het voor het record uit 'orders' over gaat.

De gruwelijk lelijke COALESCE en 999999 zijn nodig omdat de subquery voor customers met minder dan drie records een NULL zal teruggeven. In die gevallen willen we juist alle records hebben omdat het er toch al minder dan vier zijn, dus vervang ik de NULL door een belachelijk groot getal. Hier kun je ook de maximum waarde van het kolomtype in zetten om zeker te weten dat je nooit de foutin gaat.

Dingen om op te letten.

Subqueries in het SELECT deel en performance.

Subqueries in het SELECT deel van de hoofdquery worden uitgevoerd voor elk resultaat dat de hoofdquery oplevert. Dat kan in sommige gevallen veel sneller zijn dan een JOIN, maar in sommige gevallen is het juist weer trager. Meet de snelheid van de twee alternatieven en gebruik de snelste, niet de "mooiste" oplossing. Om je queries mooi te houden gebruik je VIEWs; de smerige oplossing zet je in een VIEW en die VIEW geef je een mooie nette naam en die gebruik je in je mooie nette query. Op die manier zie je de smerige query niet meer en kun je die later nog optimaliseren en/of opschonen zonder dat je de aanroepende queries hoeft aan te passen.