[SQL SERVER] L’instruction WITH ou l’utilisation de la common table expression (CTE)
WITH permet de créer une table virtuelle qui contient l’ensemble de résultats d’une requête SELECT c’est ce que l’on appelle une common table expression ou (CTE).
Par contre il faut savoir que la portée de cette table virtuelle est définie au sein de l’étendue d’exécution d’une seule instruction SELECT, INSERT, UPDATE ou DELETE. Cette clause peut donc être insérée dans la définition d’une vue comme faisant partie de l’instruction qui la constitue.
Voici l’exemple : dans la base de données AdventureWorks, je veux savoir combien de collaborateurs dirige chaque manager.
Ok, maintenant je voudrais me servir de cet ensemble de résultat en tant que table pour effectuer une jointure avec la table « HumanResources.EmployeeDepartmentHistory » pour savoir en fonction de la date d’entrée dans le service si il y a des manager qui ont eut plus de collaborateurs que d’autres
Pour faire ça on va donc transformer l’ensemble de lignes retournées précédemment en common table expression grâce à la commande WITH :
WITH toto(boss, nbre_de_sous_fifre) AS
(
select ManagerID, count(*)
from HumanResources.Employee
group by ManagerID
)
Voilà donc l’ensemble de résultat est maintenant dans la table toto (il est bien connu des développeurs que les démos marchent toujours mieux avec toto ;) et toto possède 2 colonnes qui sont boss, nbre_de_sous_fifre donc maintenant je peux associer ma table toto à une autre requête directement après la parenthèse fermante du WITH qui sera :
WITH toto(boss, nbre_de_sous_fifre) AS
(
select ManagerID, count(*)
from HumanResources.Employee
group by ManagerID
)
select boss, nbre_de_sous_fifre,StartDate from HumanResources.EmployeeDepartmentHistory
inner join toto on toto.boss=HumanResources.EmployeeDepartmentHistory.EmployeeID
where nbre_de_sous_fifre>10
order by boss
Donc on voit bien que toto est reconnue à part entière comme étant une table.
Voici le résultat :
Pour la petite histoire on voit bien que le boss 16 est rentré avant le boss 21 et que ce dernier manage plus de collaborateur que le boss 16.
Au début de cet article j’ai signalé le point noir de cette commande qui est sa portée en effet l’existence de cette table virtuelle ne concerne que l’instruction qui suit la parenthèse fermante du WITH donc si je mets un autre select il plante car il n’arrive pas à atteindre la table toto.
On voit bien que SQL signale de suite que lors du deuxième appel à toto qu’il ne connait plus cette entité.