Common table expressions

In deze blogpost gaan we in op common table expressions in SQL. Voor wie pas net SQL leert is dit een meer gevorderd onderwerp.

Wil je op je eigen computer meedoen met deze tutorial? Volg dan deze stappen om SQL en PgAdmin te installeren en de voorbeeld IMDb-database in te laden.

We zullen onderstaande onderwerpen behandelen:

• Wat is een Common Table Expression (CTE)?
• Voorbeeld Common Table Expression
• Syntax van Common Table Expressions
• Voordelen van Common Table Expressions

Wat is een Common Table Expression (CTE)?

Eerder hebben we naar subqueries gekeken. Je kunt een query binnen een andere query uitvoeren. Dit heet een subquery.

Je gebruikt subqueries om complexere vraagstukken op te lossen. Maar naarmate je meer of langere queries schrijft, kan het gebruik van subqueries onoverzichtelijk worden.

Om code te schrijven die beter leesbaar is, kun je common table expressions gebruiken. Afgekort: CTE.

We gaan vanuit een voorbeeld bekijken waarom dit handig is.

Ook interessant: wat is een relationele database?

Voorbeeld Common Table Expression

We gaan met een vraagstuk aan de slag.

Hier gaan we uiteindelijk een common table expression gebruiken.

We bouwen het stap voor stap op.

• Eerst kijken we naar losse queries.
• Dan combineren we deze met subqueries.
• Tot slot herschrijven we het met een common table expression.

Vraagstelling voor het voorbeeld

Op deze pagina vind je een uitgebreide omschrijving van deze dataset

We gaan het volgende probleem oplossen:

• In tabel movies hebben we details van films. Bijvoorbeeld het jaar, en de score.
• We willen een overzicht van alle films. Met daaraan toegevoegd: de gemiddelde score van alle films uit het jaar waarin een film uitkwam.

Hiermee kunnen we zien of een film beter/slechter was dan de gemiddelde film uit dat jaar.

Query alle films

Met de volgende SELECT FROM query halen we details van films op.

Uit tabel movies.

SELECT
  *
FROM
  movies;

Columns: 4
Rows: 36

Query gemiddelde score per jaar

Met de volgende query halen de gemiddelde score per jaar op:

SELECT
  year,
  AVG(rank) AS avg_rank_by_year
FROM
  movies
GROUP BY
  year
ORDER BY
  year;

Columns: 2
Rows: 20

Zie voor meer info:

• Info over aggregate functions als AVG()
• Info over GROUP BY
• Info over ORDER BY

Combineren met een subquery

Met een LEFT JOIN en een subquery combineren we de 2 queries.

SELECT
  m.*,
  year_details.avg_rank_by_year
FROM
  movies AS m
  LEFT JOIN (
    SELECT
      year,
      AVG(rank) AS avg_rank_by_year
    FROM
      movies
    GROUP BY
      year
  ) AS year_details ON m.year = year_details.year;

Columns: 5
Rows: 36

Je ziet dat we nu de gemiddelde score per jaar hebben toegevoegd.

Maar, de query is door de complexiteit wat lastig te lezen.

Gebruik van een common table expression

We vervangen de subquery nu door een common table expression.

We leggen hierna uit hoe een common table expression werkt.

WITH year_details AS (
    SELECT
      year,
      AVG(rank) AS avg_rank_by_year
    FROM
      movies
    GROUP BY
      year
  )
SELECT
  m.*,
  year_details.avg_rank_by_year
FROM
  movies AS m
  LEFT JOIN year_details ON m.year = year_details.year;

Columns: 5
Rows: 36

Je ziet dat we hetzelfde resultaat krijgen als met gebruik van de subquery.

• Maar door de common table expression is de code mooier in losse blokken opgedeeld.
• Dit maakt de code leesbaarder.

Common table expression syntax

We hebben een voorbeeld van een common table expression bekeken.

Daarin kun je de volgende syntax herkennen:

WITH <cte_name> AS (
  <cte_query>
)
SELECT
  *
FROM
  <cte_name>
WHERE
  ...

Je doet hiermee het volgende:

• Je geeft een query een naam. Met WITH en AS.
• Dit creeërt een tijdelijke tabel onder deze naam.
• Met de naam kun je de gegevens (de uitkomst van de query), later hergebruiken.
• Dit kan op meerdere plaatsen in een andere query.
• Bijvoorbeeld in een WHERE of JOIN statement.

Een CTE lijkt qua gebruik op een SQL CREATE VIEW. Een SQL view is als het ware een CTE die je ook buiten de query kunt blijven gebruiken.

Voordelen van common table expressions

Common table expressions hebben de volgende voordelen:

• Beter leesbare queries. Dankzij het opdelen in losse blokken.
• Hierdoor is je query ook makkelijker te testen/debuggen.
• Queries hergebruiken. Je kunt een CTE op meerdere plaatsen hergebruiken.
• Houvast bij complexe queries.

Samenvatting common table expressions in SQL

• Een common table expression (CTE) geeft een query een naam.
• Met de naam is de query vervolgens elders te gebruiken
• Voorbeeld:

WITH <cte_name> AS (
<cte_query>
)
SELECT
*
FROM
<cte_name>
WHERE
...;

• Een common table expression maakt je code beter leesbaar.

Wil je nog veel meer leren over SQL? Bekijk dan onze SQL cursus voor data analyse of download onderstaande opleidingsbrochure. In onze SQL cursus wisselen theorie en praktijkoefening elkaar steeds af. Nadat je deze training hebt gevolgd kun je zelfstandig met SQL werken. Je kunt ook altijd even contact opnemen via info@datasciencepartners.nl of 020 - 24 43 146 als je een vraag hebt.