Apprendre à utiliser manipuler les CTE (Common Table Expression),

Un tutoriel de Lyche

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 :

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

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.


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 :

212041

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

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.

212042

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).

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


212043

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. :
;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.

212044

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.

;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.
;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.

;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 :

;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 :

212042

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


;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.)