Requête récursive avec une CTE

Requête récursive avec une CTE

Cet article présente le concept de requête SQL récursive. Les requêtes récursives reposent sur les CTE (Common Table Expression) de type récursif. Nous verrons que cela peut nous éviter de faire des requêtes SQL en cascade et ainsi simplifier notre code!

Quand faire une requête récursive?

Le cas d’utilisation typique d’une requête récursive est lorsque l’on récupère des données avec un lien parent-enfant.

Prenons un exemple, nous avons une table « personne », avec un lien de parenté:

IdNameparent
0rogernull
1dominique0
2cyril1
3louisnull
4paul3
5stevens4

Lorsque l’on veut récupérer une personne et tous ses parents/ancêtres, la solution classique est d’exécuter une requête en boucle:

personne courante = cyril
While (personne courante a un parent)
	personne courante = Select * from personne where id = id personne courante

On peut récupérer une personne et tous ses parents/ancêtres avec une seule requête récursive! Cela évite du code, et une seule requête est exécutée.

Au fait, c’est quoi une CTE ?

Une CTE (Common Table Expression) est un jeu de données (En gros une sous-requête) temporaire et nommé que l’on peut utiliser dans une instruction SELECT, INSERT, UPDATE ou DELETE.

L’utilisation des CTE est pertinente lorsque l’on se retrouve à utiliser plusieurs fois la même sous-requête. C’est également utile lorsque l’on a beaucoup de sous-requêtes, dans ce cas de figure les CTE permettent de rendre la requête plus lisible.

Concrètement voici un exemple de CTE:

WITH
	cte1 AS (SELECT a, b FROM table1),
	cte2 AS (SELECT a, d FROM table2)
SELECT b, d 
	FROM cte1 
	JOIN cte2 ON cte1.a = cte2.a

Pour définir une CTE, il faut ajouter une clause WITH en début de la requête SQL. Dans cet exemple, on a créé deux CTE, nommés cte1 et cte2.

Et une CTE récursive ?

Une CTE récursive est une requête CTE qui va avoir une sous-requête qui fait référence à son propre nom! Grâce à cela, ce type de requête charge des données en récursif.

Une CTE récursive a cette structure:

  • La clause WITH doit commencer par « WITH RECURSIVE« 
  • La sous-requête récursive CTE comporte deux parties, séparées par « UNION [ALL] » ou « UNION DISTINCT« .
    • La sélection initiale. Ce SELECT ne fait pas référence au nom de la CTE.
    • La deuxième partie est récursive en faisant référence au nom de la CTE dans sa clause FROM.

Cas concret d’une CTE récursive

La requête récursive permettant de charger les données de la personne « cyril » et de tous ses parents/ancêtres est assez simple:

WITH RECURSIVE cte_personne AS (
	SELECT id, parent_id FROM personne WHERE id = 2
UNION

	SELECT personne.id, personne.parent FROM personne JOIN cte_personne ON cte_personne.id = personne.parent
) 
SELECT * FROM cte_personne

Explications:

  • WITH RECURSIVE cte_personne: On fait une CTE récursive nommée cte_personne.
  • SELECT id, parent_id FROM personne WHERE id = 2: C’est la requête initiale
  • UNION SELECT … JOIN cte_personne: C’est la partie récursive de la CTE.

Conclusion

Les CTE récursives sont bien pratiques, et permettent d’éviter du code et des requêtes en cascade.

Attention toutefois, le SQL est parfois moins maintenable que du code, surtout si la requête est à rallonge.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *