2 – Une simple expression de table (CTE : Common Table Expression)

non persistante. L’utilisation classique du concept de CTE est de rendre plus clair l’écriture de requêtes complexes bâties à partir de résultats d’autres requêtes.

Voici un exemple basique :

— creation de la table
CREATE TABLE T_NEWS
(NEW_ID INTEGER NOT NULL PRIMARY KEY,
NEW_FORUM VARCHAR(16),
NEW_QUESTION VARCHAR(32))
GO

— population de la table
INSERT INTO T_NEWS VALUES (1, ‘SQL’, ‘What is SQL ?’)
INSERT INTO T_NEWS VALUES (2, ‘SQL’, ‘What do we do now ?’)
INSERT INTO T_NEWS VALUES (3, ‘Microsoft’, ‘Is SQL 2005 ready for use ?’)
INSERT INTO T_NEWS VALUES (4, ‘Microsoft’, ‘Did SQL2000 use RECURSION ?’)
INSERT INTO T_NEWS VALUES (5, ‘Microsoft’, ‘Where am I ?’)

— la requête exprimée de manière traditionnelle :
SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
FROM T_NEWS GROUP BY NEW_FORUM
HAVING COUNT(NEW_ID) = ( SELECT MAX(NEW_NBR)
FROM ( SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
FROM T_NEWS GROUP BY NEW_FORUM ) T )

— le resultat :
NEW_NBR         NEW_FORUM
 ———–              —————-
3                           Microsoft

Cette requête est assez classique dans le cadre d’un modèle de données de type "forum". Le but est de trouver la question qui a provoqué le plus de réponse. Pour exprimer une telle requête il faut faire un MAX(COUNT( ce qui n’est pas autorisé dans SQL du fait des groupages et doit donc être résolut par l’utilisation de sous requêtes. Mais notez que dans cette écriture, deux des SELECT présentent, à peu de choses près, la même structure :

SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
FROM T_NEWS
GROUP BY NEW_FORUM

L’utilisation du concept de CTE va rendre la requête plus lisible :

WITH Q_COUNT_NEWS (NBR, FORUM) AS (SELECT COUNT(NEW_ID),
NEW_FORUM FROM T_NEWS GROUP BY NEW_FORUM)
SELECT NBR, FORUM FROM Q_COUNT_NEWS
WHERE NBR = (SELECT MAX(NBR)
FROM Q_COUNT_NEWS)

En fait nous utilisant la vue éphémère Q_COUNT_NEWS introduite par le mot clef WITH, pour écrire d’une manière plus élégante, la solution à notre problème.

Comme dans la cadre d’une vue SQL, vous devez nommer la CTE et vous pouvez donner des noms particuliers aux colonnes du SELECT qui construit l’expression de la CTE, mais cette dernière disposition n’est pas obligatoire. Dans les faits on peut enchaîner deux, trois ou autant de CTE que vous voulez dans une même requête, chaque CTE pouvant être construite à partie des expression des CET précédentes.
 Voici un exemple de ce concept de CTE gigogne :

WITH Q_COUNT_NEWS (NBR, FORUM) AS (SELECT COUNT(NEW_ID), NEW_FORUM
FROM T_NEWS
GROUP BY NEW_FORUM), Q_MAX_COUNT_NEWS (NBR) AS (SELECT MAX(NBR)
FROM Q_COUNT_NEWS) SELECT T1.*
FROM Q_COUNT_NEWS T1
INNER JOIN Q_MAX_COUNT_NEWS T2 ON T1.NBR = T2.NBR

Cette requête donne le même résultat que les précédentes : la première CTE, Q_COUNT_NEWS est utilisée comme s’il s’agissait d’une table dans la seconde CTE, Q_MAX_COUNT_NEWS. Ces deux CTE sont jointes dans l’expression de requête pour donner le résultat. Notez la virgule qui sépare les deux expression de table.