APSQL

Les tables CTE

SQL Server 2005 la notion de Common Table Expression (CTE) qui peuvent être définie comme une vue locale à une requête. C'est à dire que la définition de la table CTE n'est pas conservée dans la base. La table CTE est identifiée par un nom et elle est définie juste avant sont utilisation par une requête dans le même lot d'instruction.

La syntaxe de définition d'une table CTE est la suivante:
WITH nomTableCTE AS
(requeteSelect)
SELECT listeColonne
FROM nomTableCTE, ...

Pour illustrer les tables CTE, les exemples vont être basés sur la table suivante:

Schéma table des employés

Cette table représente un ensemble d'employés. Chaque employé est parfaitement identifié par son numéro, il possède un nom, appartient à un service, touche un salaire et on connait son supérieur hiérarchique direct. Après l'ajout de quelques lignes la table contient les informations suivantes:

Informations contenues dans la table des employés

Utilisez les tables CTE pour les sous requêtes

La première utilisation possible des tables CTE est de simplifier l'écriture des requêtes complexes, c'est à dire celle qui nécessite l'utilisation de sous requête.

Par exemple, pour connaitre la liste des employés qui possèdent un salaire supérieur au salaire moyen de leur service, il est possible d'exécuter la requête suivante:

SELECT e1.*
FROM employes e1
WHERE e1.salaire >(SELECT AVG (salaire)
FROM employes e2
WHERE e2.service=e1.service);

Avec l'utilisation d'une table CTE, la requête est plus simple à écrire et elle devient:

WITH cteSalaire(service, salaireMoyen) AS(
SELECT service, AVG(salaire)
FROM employes
GROUP BY service)
SELECT e.*
FROM employes e, cteSalaire c
WHERE e.service=c.service
AND e.salaire>c.salaireMoyen;

Le résultat de l'exécution de cette requête est alors

Exemple sous requete CTE

Utilisez les tables CTE pour les requêtes récursives

Une autre utilisation possible des tables CTE est de simplifier (considérablement) la conception des requêtes récursives. Ce type de requête est particulièrement utile pour faire sortir les informations suivant un ordre hiérarchique bien précis. Dans l'exemple exposé ici, une requête de ce type peut être écrire pour faire ressortir l'organigramme des employés. Cette opération est habituellement réalisée avec les instructions START WITH, PRIOR et CONNECT BY. Mais avons le simplement, elles ne sont pas des plus faciles à mettre en place.

Dans ce cas précis, la table CTE est constituée de 2 requêtes. La première correspond au point de départ de la requête hiérarchique, c'est à dire qu'elle permet de d'identifier le sommet de la hiérarchie. La seconde requête, quand à elle permet de parcourir l'arbre.

La structure génarale de la table CTE est alors:
WITH nomTableCTE AS
(requeteDépart
UNION ALL
requeteParcours)
SELECT listeColonne
FROM nomTableCTE, ...

Dans l'exemple présenté ici, la table CTE est constituée d'une première requête qui identifie le sommet de l'arbre (c'est à dire l'employé qui n'a pas de supérieur hiérarchique), ainsi que de la requête qui permet de parcourir l'ensemble de l'arbre. La requête qui utilisa la table CTE s'appuie sur les informations stockées dans la table d'origine et effectue une jointure avec celles issues de la table CTE. Ainsi présenté, les requêtes récursives sont beaucoup plus facile à écrire et à concevoir.

Exemple requete recursive

Les scripts