Les sous-requêtes et leurs utilisationsIl 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 |
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'; |
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.)