Apprendre à utiliser manipuler les CTE (Common Table Expression),
Un tutoriel de Lyche

Le , par Lyche, Expert confirmé
Les sous-requêtes et leurs utilisations

Il est assez difficile de traiter d’un sujet comme les tables dérivées et les CTE (Common Table Expression) sans cas concrets. Les requêtes exemples ne sont pas toujours pertinentes et l’utilité des techniques comme les Tables Dérivées (que je nommerai TD dans la suite de l’article) n’est pas intuitive. N’hésitez pas à apporter des remarques dans les commentaires, je ferai en sorte d’y répondre rapidement.

Présentation

Suite à la rédaction de mon premier billet sur les agrégats, il m’a été demandé d’expliquer ce qu’étaient les CTE (Common Table Expression ou Expression de Tables Communes).
Je vais profiter de ce billet pour donner une petite explication du terme ainsi que les comparer en termes d’utilité et de lisibilité à leurs ancêtres que sont les Tables dérivées.
Côté performance, il n’y a pas d’écart à code égal. Je ne m’étendrai pas sur ce sujet, d’autant que mon but est surtout la découverte de cette technique méconnue.

Définition

Les tables dérivées sont souvent, à tort, appelées « sous-requêtes », une requête ne pouvant être appelée sous-requête que lorsqu’elle est située dans une clause Where.
Les tables dérivées sont des requêtes permettant d’extraire un jeu de données exploitable dans une requête de niveau supérieur.
Structure de données :

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE DATABASE DBTrainningCTE; 
GO 
CREATE TABLE dbo.TB_DEPARTEMENTS ( 
  ID_DEPARTEMENT  INT PRIMARY KEY 
, NOM_DEPARTEMENT VARCHAR( 50 ) ); 
GO 
  
CREATE TABLE dbo.TB_EMPLOYES ( 
  ID_EMPLOYE      INT PRIMARY KEY 
, NOM_EMPLOYE     VARCHAR( 50 ) NOT NULL 
, AGE_EMPLOYE     INT 
, DEPARTEMENT_ID  INT 
, SALAIRE_EMPLOYE MONEY ); 
GO 
  
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 1, 'Administration'       ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 2, 'Ressources Humaines'  ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 3, 'Service Informatique' ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 4, 'Comptabilité'         ); 
  
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 1, 'Georges', 74, 4, 2480.3 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 2, 'Pierre' , 17, 3, 1387.2 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 3, 'Bernard', 63, 1, 3499.8 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 4, 'John'   , 23, 3, 1876.9 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 5, 'Jérome' , 45, 2, 2286.6 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 6, 'Lina', 30, 3, 2230.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 7, 'Marie', 26, 3, 1980.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 8, 'Virginie', 37, 3, 2730.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 9, 'Hélène', 33, 3, 2430.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES ( 10, 'Yuva', 28, 3, 2200.2 );


Exemple

Code sql : Sélectionner tout
1
2
3
4
SELECT * 
  FROM ( SELECT Nom, Prenom 
           FROM dbo.TB_EMPLOYES ) AS TD --TD Pour Table dérivée, mais je recommande un nom explicite pour les futurs lecteurs de votre code ;) 
 WHERE Nom = 'toto';
Ceci est un exemple simple de table dérivée.

Cas pratique

Notre exemple simplifié doit nous permettre d’envisager l’élaboration d’une requête qui va lister un nombre de personnes.
Listons, pour commencer nos employés dont l’âge est compris entre 20 et 40 ans.

Code sql : Sélectionner tout
1
2
3
4
5
  
SELECT NOM_EMPLOYE   , AGE_EMPLOYE    , 
       DEPARTEMENT_ID, SALAIRE_EMPLOYE 
  FROM dbo.TB_EMPLOYES 
 WHERE AGE_EMPLOYE BETWEEN 20 AND 40;

Le résultat sera le suivant :


Ce résultat, peut être placé dans un « FROM », comme nous l’avons vu plus haut.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
 SELECT * 
  FROM ( 
         SELECT NOM_EMPLOYE   , AGE_EMPLOYE    , 
                DEPARTEMENT_ID, SALAIRE_EMPLOYE 
           FROM dbo.TB_EMPLOYES 
          WHERE AGE_EMPLOYE BETWEEN 20 AND 40 ) TD 
 WHERE SALAIRE_EMPLOYE > 2000

Si nous appliquons un filtre, on peut constater que certaines lignes vont être ignorées par l’application de la condition sur le salaire.


De façon simplifiée, voici comment on exploite une Table Dérivée. Nous pouvons aussi rajouter des jointures sur ces tables, puisque, le principe est de pouvoir les exploiter exactement comme s'il s’agissait d’un élément physique de votre base. (Vue, table).

Code sql : Sélectionner tout
1
2
3
4
5
6
7
SELECT NOM_EMPLOYE, NOM_DEPARTEMENT, AGE_EMPLOYE 
  FROM ( 
         SELECT NOM_EMPLOYE   , AGE_EMPLOYE    , 
                DEPARTEMENT_ID, SALAIRE_EMPLOYE 
           FROM dbo.TB_EMPLOYES 
          WHERE AGE_EMPLOYE BETWEEN 20 AND 40 ) TD 
        INNER JOIN dbo.TB_DEPARTEMENTS D ON TD.DEPARTEMENT_ID = D.ID_DEPARTEMENT --Ici, la jointure se fait sur le résultat de la TD et sur l'ID_DEPARTEMENT de la table TB_DEPARTEMENT

Le résultat montre bien que la jointure se fait exactement de la même façon que sur une requête « classique ».
Maintenant, refaisons le même exercice, avec les CTE

Définition

Une CTE (ou Table d’Expression Commune) est une requête utilisée en tant que table à « usage unique ». C’est-à-dire qu’une fois déclarée, la CTE ne pourra être exploitée qu’immédiatement après.
Ex. :
Code sql : Sélectionner tout
1
2
3
4
5
6
;WITH maCTE AS ( 
SELECT * 
  FROM maTable ) 
  
SELECT * 
  FROM maCTE;

Si la moindre instruction SQL se glisse entre votre CTE et votre requête l’exploitant, une erreur de syntaxe sera renvoyée.


Il est cependant possible d’enchaîner plusieurs CTE en les séparant par une virgule et d’exploiter toutes ces CTE depuis une même requête située immédiatement sous votre code.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
;WITH maCTE AS ( 
SELECT * 
  FROM maTable ) 
, maCTE2 AS ( 
SELECT * 
  FROM maTable ) 
  
SELECT * 
  FROM maCTE 
       INNER JOIN maCTE2 ON maCTE.Clé = maCTE2.Clé2;

N. B. Une CTE peut aussi en appeler une autre située au-dessus dans le code.
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
;WITH maCTE AS ( 
SELECT * 
  FROM maTable ) 
, maCTE2 AS ( 
SELECT * 
  FROM maCTE 
       INNER JOIN maTable2 ON maCTE.Clé = maTable2.Clé2 ) 
  
SELECT * 
  FROM maCTE2

L’utilité des CTE réside à mon humble avis, dans cette capacité de pouvoir organiser votre code en blocs sans surcoût au niveau des plans d’exécution. Une écriture claire avec des petits blocs spécifiques vous permet d’intervenir aisément sur une portion précise sans avoir à revisiter tout votre code.

Attention, l’effet pervers de la multiplicité des petites requêtes peut finalement rendre illisible votre code, à chacun de trouver le bon compromis.
Bien entendu, leur utilité ne s’arrête pas là. Nous détaillerons dans un prochain billet une utilisation avancée des CTE avec la récursion et les calculs hiérarchiques.

Cas Pratique

Comme pour les tables dérivées, nous allons voir comment nous pouvons l’utiliser de façon rudimentaire.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
;WITH maCTE AS ( --maCTE pour l'exemple, mais comme pour les TD, je recommande un nom explicite. 
SELECT FirstName, LastName 
  FROM dbo.TB_EMPLOYES 
) 
SELECT * 
  FROM maCTE --Appel à la CTE de la même façon que si c'était une table 
 WHERE Nom = 'toto';

N. B. Il n’y a aucune différence en termes de performance entre ces deux procédés.
N.B. Une CTE ne peut fonctionner que si la précédente instruction se termine par un « ; » Personnellement, je le colle toujours devant la balise WITH.

Cas Pratique

Reprenons notre code précédent et utilisons une CTE.
Nous obtenons donc ceci :

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
;WITH maCTE AS ( 
SELECT NOM_EMPLOYE   , AGE_EMPLOYE    , 
       DEPARTEMENT_ID, SALAIRE_EMPLOYE 
  FROM dbo.TB_EMPLOYES 
 WHERE AGE_EMPLOYE BETWEEN 20 AND 40 ) 
  
SELECT * 
  FROM maCTE 
 WHERE SALAIRE_EMPLOYE > 2000;

Le résultat, comme prévu est exactement le même qu’avec une TD :


De même, les jointures s’établissent de façon naturelle.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
  
;WITH maCTE AS ( 
SELECT NOM_EMPLOYE   , AGE_EMPLOYE    , 
       DEPARTEMENT_ID, SALAIRE_EMPLOYE 
  FROM dbo.TB_EMPLOYES 
 WHERE AGE_EMPLOYE BETWEEN 20 AND 40 ) 
  
SELECT * 
  FROM maCTE 
       INNER JOIN dbo.TB_DEPARTEMENTS D ON maCTE.DEPARTEMENT_ID = D.ID_DEPARTEMENT

Nous voyons donc que notre requête finale se simplifie et que notre code peut être facilement identifiable.

  • Un nom de CTE Explicite
  • Quelques commentaires pour faciliter la lecture
  • Un nombre raisonnable de niveaux de CTE


Et vous aurez toutes les clés pour réussir une procédure stockée performante et maintenable ! (Par vous, comme par d’autres personnes.)


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster un commentaire

Avatar de jimbolion jimbolion - Expert éminent https://www.developpez.com
le 07/06/2016 à 16:52
Merci encore pour cet article qui éclaire de façon constructive l'utilisation des sous requêtes et CTE
Avatar de Gecko Gecko - Membre éprouvé https://www.developpez.com
le 08/06/2016 à 15:03
Franchement sympa comme article
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 08/06/2016 à 15:12
Citation Envoyé par jimbolion;bt2825
Merci encore pour cet article qui éclaire de façon constructive l'utilisation des sous requêtes et CTE

Merci a toi pour l'aide
Avatar de fozzeuh fozzeuh - Membre confirmé https://www.developpez.com
le 28/07/2016 à 13:45
Vraiment sympa comme article, merci Lyche.
Avatar de Jitou Jitou - Membre averti https://www.developpez.com
le 24/08/2016 à 13:32
C'est un peu comme l'utilisation d'une vue sauf que l'on ne garde pas la sous requête en mémoire ? Sinon article très bien écrit et facile à comprendre.
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 28/08/2016 à 20:56
Citation Envoyé par Jitou;bt3045
C'est un peu comme l'utilisation d'une vue sauf que l'on ne garde pas la sous requête en mémoire ? Sinon article très bien écrit et facile à comprendre.

Il n'y a en effet aucune persistance du code au niveau du SGBD comme une vue pourrait le faire. Cependant, les plans d'exécution et la mise en cache des données extraite s'applique comme lors de l'utilisation d'une vue.
Avatar de julien94320 julien94320 - Membre confirmé https://www.developpez.com
le 21/09/2016 à 14:42
Merci monsieur pour cet article très bien réalisé

Julien
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 21/09/2016 à 15:51
Citation Envoyé par julien94320;bt3133
Merci monsieur pour cet article très bien réalisé

Julien

Merci à toi pour l'encouragement
Avatar de alassanediakite alassanediakite - Membre émérite https://www.developpez.com
le 03/10/2016 à 20:23
Salut
Pouvez-vous détailler plus ...
Les tables dérivées sont souvent, à tort, appelées « sous-requêtes », une requête ne pouvant être appelée sous-requête que lorsqu’elle est située dans une clause Where.

... ou plutôt donner des références de cette affirmation.
Du courage pour d'autres articles
@+
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 06/10/2016 à 17:33
Citation Envoyé par alassanediakite;bt3164
Salut
Pouvez-vous détailler plus ...

... ou plutôt donner des références de cette affirmation.
Du courage pour d'autres articles
@+

Bonjour,

Une référence fiable peut être trouvée ici http://www.programmerinterview.com/i...e-vs-subquery/

J'ai probablement été un peu "sec" dans mon propos et je corrigerais ça afin de paraitre moins catégorique si cela gène . Néanmoins, je vais faire quelques recherches afin de confirmer ou infirmer cette question.

Cordialement,
Offres d'emploi IT
Data scientist senior H/F
Safran - Ile de France - Magny-les-Hameaux (Saclay)
Ingénieur développement fpga (traitement vidéo) H/F
Safran - Ile de France - 100 rue de Paris 91300 MASSY
Architecte et intégrateur scade/simulink H/F
Safran - Ile de France - Vélizy-Villacoublay (78140)

Voir plus d'offres Voir la carte des offres IT
Contacter le responsable de la rubrique SGBD & SQL