Un Common Table Expression, ce n'est pas juste une syntaxe SQL de plus. C'est un test de Rorschach pour développeur. La façon dont tu écris et utilises tes CTE en dit plus long sur tes priorités que n'importe quelle ligne de ton CV. Et devine quoi ? Le secteur entier est en train de passer ce test.
SQL a longtemps été traité comme le parent pauvre du développement web. On enchaînait les ORM, on fuyait les requêtes complexes, on préférait trois appels API plutôt qu'une jointure un peu velue. Puis quelque chose a changé. Les CTE sont devenues le symbole d'un retour aux sources — et d'une maturité nouvelle. La Stack Overflow Developer Survey le confirme année après année : SQL reste parmi les compétences les plus demandées, loin devant les frameworks à la mode. Ce n'est pas un hasard.
Mais les CTE ne sont pas qu'un outil technique. Elles cristallisent des choix de valeurs. Tu utilises un WITH bien nommé pour découper une requête complexe ? Tu montres que la maintenabilité prime sur ton ego de codeur. Tu balances une sous-requête illisible de 20 lignes dans le WHERE ? Tu assumes que la prochaine personne qui lira ça est un expert — ou un masochiste.
Et puis il y a la fragmentation. PostgreSQL et MySQL ne traitent pas les CTE de la même manière, et cette divergence en dit long sur les philosophies des deux communautés. Dans cet article, je te propose de dérouler tout ça : d'abord les fondamentaux, ensuite la maintenabilité, puis les disparités entre les deux moteurs, et enfin les stratégies d'optimisation via le plan d'exécution et les pièges des CTE récursives.
On parle souvent de SQL comme d'un langage stable, immuable. Les CTE racontent une autre histoire : celle d'un écosystème qui bouge, qui se réinvente, et qui impose des standards de plus en plus élevés. Installe-toi, prends un café, et regardons ensemble ce que nos requêtes révèlent de nous.
Qu'est-ce qu'une CTE et pourquoi les utiliser ?
Une CTE — Common Table Expression — c'est une sous-requête nommée, définie avec WITH, que tu peux réutiliser dans la requête principale. Concrètement, tu donnes un nom à un jeu de résultats temporaire, et tu le traites comme une table. Ça paraît anodin. Sauf que ça transforme des requêtes monstres en pipeline d'étapes lisibles.
Sans CTE, tu écris la même chose avec une sous-requête dans le FROM. Mais dès que la logique se complexifie — les sous-requêtes imbriquées, les agrégats intermédiaires, les jointures multiples — tu te retrouves vite avec du SQL illisible et impossible à maintenir. La CTE te force à décomposer le raisonnement en étapes nommées. Relire une CTE bien nommée prend 5 secondes. Relire une sous-requête de 20 lignes peut te ruiner une journée.
Mon avis personnel : adopte les CTE dès qu'une requête dépasse deux jointures ou contient un calcul intermédiaire. Pas pour la performance — souvent c'est neutre — mais pour la sécurité mentale de l'équipe qui devra maintenir ton code derrière toi. C'est aussi le premier pas vers des techniques plus avancées, comme les CTE récursives, qui changent vraiment la donne sur PostgreSQL.
Comment les CTE améliorent-elles la maintenabilité des requêtes complexes ?
La vraie force des CTE, c'est qu'elles te forcent à nommer tes étapes. Une sous-requête de 20 lignes dans le WHERE, c'est une boîte noire. Avec un WITH bien nommé, tu transformes cette boîte noire en brique réutilisable, testable et remplaçable. Et ça, c'est de la maintenance concrète.
Regarde ce que ça fait sur ta façon de travailler. Tu peux déboguer chaque CTE séparément en remplaçant la requête principale par un simple SELECT. Tu peux la commenter, la modifier, la réutiliser dans un autre contexte. Sans CTE, tu serais en train d'extraire des sous-requêtes à la main pour comprendre ce que fait le SQL.
Mon expérience ? Chaque fois que j'ai refactoré une requête monstrueuse avec des CTE, le temps de maintenance s'est effondré. Les collègues comprennent l'intention métier avant de plonger dans la mécanique SQL. Et les bugs se voient au premier coup d'œil, parce qu'une étape mal nommée ou un agrégat erroné saute aux yeux. Si tu veux rendre ton code SQL durable, découpe-le en CTE, donne-leur des noms qui racontent une histoire, et interdis-toi les sous-requêtes dans le WHERE.
Quelles sont les différences d'implémentation des CTE entre PostgreSQL et MySQL ?
Tiens-toi bien : PostgreSQL supporte les CTE depuis 2005. MySQL n'a rattrapé son retard qu'en 2018 avec la version 8.0. Treize ans d'écart, et ça se voit. Les deux SGBD ne parlent pas le même langage quand il s'agit de WITH.
La syntaxe de base est la même, mais les divergences commencent dès que tu parles de récursivité. PostgreSQL accepte WITH RECURSIVE et sait aussi déduire la récursivité dans certains cas. MySQL, lui, exige obligatoirement le mot-clé RECURSIVE. Sans lui, pas de CTE récursive.
Sur ce plan, la ressemblance est trompeuse. PostgreSQL traite une CTE comme une "optimization fence" : il matérialise le résultat, ce qui peut nuire aux performances si tu utilises une CTE dans une grosse jointure. Depuis PostgreSQL 12, le moteur sait parfois inline la CTE, mais pas toujours. MySQL, lui, matérialise systématiquement les CTE, point final. Tu ne peux pas le forcer à inline.
La différence qui tue : PostgreSQL permet les CTE de modification de données. Tu peux faire WITH suppr AS (DELETE FROM users WHERE ... RETURNING *) SELECT * FROM suppr. MySQL, c'est niet. Si tu espères faire ça sur MySQL, tu restes sur ta faim.
Mon avis ? Si tu bosses avec des requêtes complexes, PostgreSQL reste très en avance. MySQL a comblé le fossé pour les cas simples, mais dès que tu touches à la récursivité, à la mutation ou à l'optimisation fine, tu sens la différence.
Analyser le plan d'exécution et maîtriser les CTE récursives
Le plan d'exécution ne ment jamais. C'est lui qui te dit si ta CTE est une optimisation ou une usine à gaz. J'ai vu trop de devs refactoriser fièrement une requête en trois CTE, puis s'étonner que ça rame. EXPLAIN ANALYZE tranche immédiatement.
Première chose à regarder : est-ce que le moteur matérialise la CTE ou l'inline ? PostgreSQL 12 et plus l'inline par défaut quand c'est possible. Avant, tu héritais d'un CTE Scan matérialisé — parfois utile, parfois désastreux. Dans une requête avec des jointures, une CTE matérialisée peut fausser les estimations de cardinalité et dégrader le plan.
Joue avec MATERIALIZED et NOT MATERIALIZED, compare les coûts, regarde le nombre de lignes réel. Le gagnant devient évident. Les index seuls ne suffisent pas.
Les CTE récursives, c'est autre chose. Bien utilisées — hiérarchies, arbres, graphes — c'est l'outil le plus élégant de SQL. Mais sans garde-fou, tu offres une boucle infinie à ta base. Le réflexe : WITH RECURSIVE + UNION ALL, et toujours une condition d'arrêt explicite dans le WHERE.
Si ton graphe peut contenir des cycles, ajoute aussi une protection anti-cycle : garde un tableau des ids déjà visités et vérifie-le dans la jointure. Les CTE récursives ne sont pas un jouet. Mais correctement cadrées, elles sauvent des vies — et des requêtes.
Conclusion
Quand je vois une équipe qui écrit des CTE bien nommées, je sais qu'elle a compris l'essentiel : la lisibilité est une fonctionnalité. Des fondamentaux aux CTE récursives, tout devient une question de découpage et de clarté. Ce choix révèle ta vraie priorité.
Concrètement, j'ai réécrit l'année dernière une requête de reporting de 120 lignes en trois CTE. Le plan d'exécution PostgreSQL est resté identique. Mais le temps de relecture est passé de dix minutes à deux, et la correction d'un bug a pris trente secondes au lieu d'une heure.
Ce n'est pas qu'une question de confort : c'est une écologie du SGBD, une attention aux performances, une prise de conscience des disparités entre PostgreSQL et MySQL. Les équipes qui intègrent ces nuances dans leur style de code atteignent un autre niveau de maturité. Et celles qui ne le font pas finiront par trébucher sur leur propre complexité.