Réponse

L'instruction WITH (ou CTE - Common Table Expression) crée temporairement un ensemble de résultats que l'on peut utiliser dans le SELECT principal ou même de manière récursive. Les CTE rendent les requêtes plus compréhensibles, améliorent la lisibilité et permettent de réutiliser les résultats intermédiaires.

Avantages par rapport aux sous-requêtes classiques :

Améliorent la lisibilité des requêtes complexes (plus facile à déboguer et à maintenir).
Permettent d'utiliser des requêtes récursives.
On peut faire référence aux CTE plusieurs fois par leur nom, au lieu de dupliquer la même sous-requête.

Exemple :

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Question piège

“L'indication MATERIALIZED/NOT MATERIALIZED est-elle importante lors de la déclaration d'un CTE ? Comment cela peut-il affecter les performances ?”

Réponse : Dans PostgreSQL, on peut explicitement indiquer MATERIALIZED (calculer le CTE une fois et le stocker, même s'il est utilisé plusieurs fois) ou NOT MATERIALIZED (le CTE sera intégré dans la requête principale). Généralement, l'optimiseur choisit lui-même s'il faut matérialiser le CTE, mais une indication forcée peut parfois avoir un impact significatif sur la vitesse.

Exemple :

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Histoire

Dans un grand projet, un analyste a remplacé toutes les sous-requêtes par des CTE, sans savoir qu'avant la version 12 de PostgreSQL, les CTE étaient toujours matérialisés. En conséquence, les requêtes ont ralenti de 2 à 3 fois. Après être revenu aux sous-requêtes ou être passé à de nouvelles versions, le problème a été résolu.

Histoire

Un rapport avec plusieurs niveaux de sous-requêtes imbriquées était illisible pour un collègue. Nous l'avons réécrit en CTE avec des noms significatifs — la discussion et le soutien des requêtes se sont considérablement simplifiés.

Histoire

Deux CTE avec des noms identiques ont causé de la confusion dans un grand script : le CTE a été utilisé avant sa déclaration. En conséquence, erreur de compilation et grande perte de temps pour trouver la raison.

Que fait l'instruction WITH (Common Table Expressions, CTE) ? Quels sont les avantages de l'utilisation des CTE par rapport aux sous-requêtes ? Donnez un exemple.

Réponse

Question piège