Voir le flux RSS

Blog de Lyche

[Actualité] Les CTE

Note : 7 votes pour une moyenne de 5,00.
par , 07/06/2016 à 17h46 (1208 Affichages)
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 :

Nom : Résultat TD 1.jpg
Affichages : 2067
Taille : 30,3 Ko

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

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
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.

Nom : Résultat TD Filtrée.jpg
Affichages : 2022
Taille : 22,2 Ko

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 - Visualiser dans une fenêtre à part
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

Nom : Résultat TD & Jointure.jpg
Affichages : 2041
Taille : 31,9 Ko

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 - Visualiser dans une fenêtre à part
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.

Nom : Erreur CTE.jpeg
Affichages : 2063
Taille : 69,0 Ko

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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 :

Nom : Résultat TD Filtrée.jpg
Affichages : 2022
Taille : 22,2 Ko

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

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
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.)

Envoyer le billet « Les CTE » dans le blog Viadeo Envoyer le billet « Les CTE » dans le blog Twitter Envoyer le billet « Les CTE » dans le blog Google Envoyer le billet « Les CTE » dans le blog Facebook Envoyer le billet « Les CTE » dans le blog Digg Envoyer le billet « Les CTE » dans le blog Delicious Envoyer le billet « Les CTE » dans le blog MySpace Envoyer le billet « Les CTE » dans le blog Yahoo

Mis à jour 29/07/2016 à 09h20 par ClaudeLELOUP

Catégories
Sans catégorie

Commentaires

  1. Avatar de jimbolion
    • |
    • permalink
    Merci encore pour cet article qui éclaire de façon constructive l'utilisation des sous requêtes et CTE
  2. Avatar de Gecko
    • |
    • permalink
    Franchement sympa comme article
  3. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par jimbolion
    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
  4. Avatar de fozzeuh
    • |
    • permalink
    Vraiment sympa comme article, merci Lyche.
  5. Avatar de Jitou
    • |
    • permalink
    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.
  6. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par Jitou
    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.
  7. Avatar de julien94320
    • |
    • permalink
    Merci monsieur pour cet article très bien réalisé

    Julien
  8. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par julien94320
    Merci monsieur pour cet article très bien réalisé

    Julien
    Merci à toi pour l'encouragement
  9. Avatar de alassanediakite
    • |
    • permalink
    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
    @+
  10. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par alassanediakite
    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,