IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Blog de Lyche

[Actualité] Les CTE

Note : 8 votes pour une moyenne de 4,50.
par , 07/06/2016 à 17h46 (6275 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 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 : 52364
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 : 54361
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 : 47416
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 : 47818
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 : 54361
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 05/10/2018 à 16h50 par Lyche

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,
  11. Avatar de Wachter
    • |
    • permalink
    Bonjour,

    Serait-il possible d'utiliser une CTE (pour remplacer une sous-requête) dans une clause WHERE ?

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH cte AS
    (
      SELECT co.ClientId
      FROM Commande
    )
    
    SELECT cl.ClientId
    FROM Client cl
    WHERE cl.ClientId IN (cte)
  12. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par Wachter
    Bonjour,

    Serait-il possible d'utiliser une CTE (pour remplacer une sous-requête) dans une clause WHERE ?

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH cte AS
    (
      SELECT co.ClientId
      FROM Commande
    )
    
    SELECT cl.ClientId
    FROM Client cl
    WHERE cl.ClientId IN (cte)
    Avec beaucoup de retard suite à divers soucis.
    Oui, tout à fait, il faut simplement considérer la CTE comme une table a usage unique. Tu peux même faire des UPDATE de ta CTE qui impacterons la table. A condition que la requête n'affecte qu'une seule table.

    Cordialement,
  13. Avatar de ericung
    • |
    • permalink
    Bonjour
    Je vous remercie pour m'avoir fait découvrir les CTE !
    Les sous requêtes peuvent aussi être utilisé dans le SELECT.
    La seul condition est que ces sous tables ne retourne qu'un résultat max.
    C'est très utile dans les agrégats
    Exemple :
    SELECT E.Nom
    , (SELECT ee FROM TableSousRequete1 WHERE e = E.e) AS ee
    , (SELECT ff FROM TableSousRequete2 WHERE f = E.f) AS ff
    , (SELECT gg FROM TableSousRequete3 WHERE g = E.g) AS gg
    FROM TB_Employe AS E
    WHERE E.d = 1

    Cela permet d'écrire une requête plus simple à lire.
  14. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par ericung
    Bonjour
    Je vous remercie pour m'avoir fait découvrir les CTE !
    Les sous requêtes peuvent aussi être utilisé dans le SELECT.
    La seul condition est que ces sous tables ne retourne qu'un résultat max.
    C'est très utile dans les agrégats
    Exemple :
    SELECT E.Nom
    , (SELECT ee FROM TableSousRequete1 WHERE e = E.e) AS ee
    , (SELECT ff FROM TableSousRequete2 WHERE f = E.f) AS ff
    , (SELECT gg FROM TableSousRequete3 WHERE g = E.g) AS gg
    FROM TB_Employe AS E
    WHERE E.d = 1

    Cela permet d'écrire une requête plus simple à lire.
    Bonjour,

    Attention à ne pas abuser de sous requête dans le SELECT, les performances peuvent en être affectée.
    Mais oui, cela fonctionne aussi de cette façon

    Cordialement,
  15. Avatar de begooden-it
    • |
    • permalink
    Bonjour Lyche,

    très bon article, très clair et efficace, et surtout des exemples concrets et réalistes, pas comme quand dans les documentations des éditeurs qui donnent des exemples totalement dénués de sens :-)

    Je viens de mettre en pratique sur Informix V14.10 et tout cela marche très bien!

    N'ayant pas encore regardé les autres articles, j'avais une petite question:
    comment peut on utiliser les CTE avec des curseurs dans une application développée avec un vrai langage?
    La CTE marche très bien quand on exécute les CTE dans un éditeur SQL, mais je ne vois pas trop comment on peut, et surtout dans le cas où l'on va exécuter plusieurs requêtes sur la CTE, déclarer le curseur et envoyer le résultat dans une une variable ou structure ?