Header

Common table expressions

Introductie

Eerder hebben we naar subqueries gekeken.

Je kunt een query binnen een andere query uitvoeren.
- Dit heet een subquery.

Met een subquery kun je complexe vraagstukken oplossen.

Maar naarmate je meer en/of lange queries schrijft, kan het 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.

1. Voorbeeld CTE

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.

1.1. Vraagstelling

Header

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 waarop een film uitkwam.

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

1.2. Query alle films

Met de volgende query halen we details van films op.

Uit tabel movies.

SELECT
  *
FROM
  movies;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

1.3. 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;

year	avg_rank_by_year
1972	9
1977	8.8
1978	7.5
...	...

Columns: 2
Rows: 20

1.4. 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;

id	name	year	rank	avg_rank_by_year
10920	Aliens	1986	8.2	8.2
17173	Animal House	1978	7.5	7.5
18979	Apollo 13	1995	7.5	7.9
...	...	...	...	...

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.

1.5. 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;

id	name	year	rank	avg_rank_by_year
10920	Aliens	1986	8.2	8.2
17173	Animal House	1978	7.5	7.5
18979	Apollo 13	1995	7.5	7.9
...	...	...	...	...

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.

2. 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.

3. Voordelen van common table expressions

Common table expressions hebben de volgende voordelen:

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

Samenvatting

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.