Quelle est la différence entre les CTE et les vues SQL ?

Vues SQL et CTE : Que font-ils ? En quoi sont-elles différentes ? Laquelle devez-vous utiliser et quand ? Obtenez vos réponses ici !

En SQL, les CTE (common table expressions) et les vues permettent d'organiser vos requêtes, ce qui permet d'obtenir un code plus propre et plus facile à suivre. Cependant, il existe des différences importantes entre elles. Cet article vous présente plusieurs exemples d'expressions de table commune et de vues et vous explique quand utiliser chacune d'entre elles.

Que sont les expressions de table communes (CTE) en SQL ?

Comme vous vous en souvenez peut-être, les expressions de table commune sont des ensembles de résultats temporaires créés à l'aide d'instructions SQL simples, puis référencés dans les instructions SELECT, INSERT, UPDATE ou DELETE.

Par exemple, disons que nous avons une table appelée top_apps contenant des informations sur le classement des meilleures applications dans différentes catégories :

Nous avons également le tableau google_apps qui contient les mêmes informations pour plusieurs applications Google :

Nous souhaitons comparer les performances des applications Google à celles des applications les plus performantes dans les catégories correspondantes. Plus précisément, nous voulons que les informations relatives à l'évaluation maximale dans chaque catégorie soient affichées à côté de l'évaluation de l'application Google de la même catégorie.

Voici une requête qui permet d'atteindre cet objectif à l'aide d'un CTE :

WITH top_apps_max AS (
	SELECT category, MAX(rating) AS max_rating
	FROM top_apps
	GROUP BY category)
SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

La requête commence par créer un ensemble de résultats temporaire appelé top_apps_max. Cet ensemble de résultats est dérivé de la table top_apps et comprend la liste des catégories ainsi que les notes maximales correspondantes. Ensuite, dans l'instruction principale SELECT, nous joignons cet ensemble de résultats temporaire à la table en utilisant la colonne commune . google_apps en utilisant la colonne commune category. Voici le résultat :

Vous pourriez obtenir le même résultat en utilisant une sous-requête au lieu d'un CTE. Cependant, par rapport aux sous-requêtes, l'utilisation d'un CTE SQL donne un code plus propre et plus facile à suivre, que vous pouvez lire de haut en bas : vous créez d'abord un ensemble de résultats temporaire avec un nom spécifique qui est utilisé plus tard dans la requête pour faire référence à cet ensemble de résultats.

Notez que l'élément CTE n'existe en mémoire que pendant l'exécution de la requête. Après l'exécution de la requête, l'élément CTE est supprimé ; il ne peut pas être utilisé pour la prochaine requête SQL, sauf si nous le définissons à nouveau. Cependant, le même CTE peut être référencé plusieurs fois dans la requête principale et les sous-requêtes.

Vous pouvez en savoir plus sur les CTE SQL dans ce guide d'introduction complet sur les expressions de table communes. Et si vous souhaitez pratiquer les CTE à l'aide d'exemples concrets, consultez notre cours interactif sur les expressions de table communes.

Dans le langage courant, les CTE sont parfois appelés des vues en ligne. Rappelons donc ce qu'est une vue et en quoi elle diffère d'un CTE.

Qu'est-ce qu'une vue en SQL ?

Une vue est une requête SQL stockée qui est exécutée chaque fois que vous la référencez dans une autre requête. Notez qu'une vue ne stocke pas le résultat d'une requête particulière, mais la requête elle-même.

Voyons comment cela fonctionne. Nous allons utiliser un exemple similaire, mais cette fois-ci, nous utiliserons une vue au lieu d'un CTE.

Nous commencerons par créer la vue top_apps_max avec le mot-clé CREATE VIEW, suivi de l'instruction SELECT:

CREATE VIEW top_apps_max AS
SELECT category, MAX(rating) AS max_rating, MAX(reviews) AS max_num_reviews
FROM top_apps
GROUP BY category;

Comme vous le voyez, l'instruction SELECT est très similaire à celle que nous avons utilisée avec le CTE. Elle pourrait être absolument identique, mais nous avons changé la portée : Maintenant, nous voulons voir le nombre maximum d'avis en plus de la note maximum pour chaque catégorie (juste pour avoir plus de données avec lesquelles travailler).

Donc, maintenant nous avons une requête SQL stockée appelée top_apps_max. Il est temps de l'utiliser !

Commençons par reproduire notre premier exemple en utilisant une vue au lieu d'un CTE. Cependant, cette fois-ci :

• Nous n'avons pas besoin de créer un CTE au début de la requête, car nous avons déjà la vue stockée top_apps_max.
• Nous joignons simplement la table google_apps avec la vue top_apps_max sur la colonne catégorie et de lister les colonnes que nous voulons voir dans la sortie :

SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Le résultat de cette requête sera le même que dans notre premier exemple :

Alors, quelle est la différence ?

Tout d'abord, vous pouvez utiliser la même vue dans d'autres requêtes sans la définir à nouveau. Par exemple, la requête SQL ci-dessous fait référence à la même vue top_apps_max; cette fois, elle est utilisée pour comparer le nombre d'avis plutôt que le classement des différentes applications :

SELECT ga.name, ga.category, ga.reviews, tam.max_num_reviews
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

En outre, vous pouvez utiliser un CTE lors de la définition d'une vue. Supposons que nous voulions également voir le nom de l'application la plus populaire dans chaque catégorie (c'est-à-dire l'application ayant reçu le plus grand nombre d'avis). Une façon de le faire est de créer une vue qui affiche le nom, la catégorie et le nombre d'avis pour l'application la plus populaire dans chaque catégorie :

CREATE VIEW top_app_per_category AS
WITH top_app_max_reviews AS (
    SELECT category, MAX(reviews) AS max_num_reviews
    FROM top_apps
    GROUP BY category)
SELECT ta.name, ta.category, ta.reviews
FROM top_apps ta
JOIN top_app_max_reviews tamr
ON ta.reviews = tamr.max_num_reviews;

Comme vous le voyez, la requête stockée dans cette vue inclut une expression de table commune qui fournit le nombre maximum d'avis pour chaque catégorie. Ensuite, dans la requête principale de la vue top_app_per_category, nous joignons cette expression de table commune avec la table top_apps pour obtenir le nom de l'application ayant le plus grand nombre d'avis dans chaque catégorie.

Nous pouvons maintenant faire référence à cette vue dans une autre requête qui renvoie les applications Google avec le nom de l'application la plus populaire dans la catégorie correspondante et son nombre d'avis :

SELECT ga.name, ga.category, ga.reviews, top.name AS top_app, top.reviews AS top_app_reviews
FROM google_apps ga
JOIN top_app_per_category top
ON ga.category = top.category;

Ce qu'il faut retenir des vues SQL, c'est que, contrairement à un CTE, une vue est un objet physique dans une base de données et est stockée sur un disque. Cependant, les vues ne stockent que la requête, et non les données renvoyées par la requête. Les données sont calculées chaque fois que vous faites référence à la vue dans votre requête.

Vous souhaitez en savoir plus sur les vues SQL ? Voici un excellent article qui explique les vues SQL à l'aide de nombreux exemples et illustrations. N'oubliez pas non plus de vous entraîner sur les vues avec notre cours interactif Working with Views.

CTE SQL ou vue : Quand utiliser l'un ou l'autre

Bien qu'il existe quelques différences entre elles, les expressions de table communes et les vues semblent avoir des performances très similaires. Alors, quand devez-vous les utiliser ?

• Requêtes ad-hoc. Pour les requêtes qui sont référencées occasionnellement (ou une seule fois), il est généralement préférable d'utiliser une CTE. Si vous avez à nouveau besoin de la requête, vous pouvez simplement copier le CTE et le modifier si nécessaire.
• Requêtes fréquemment utilisées. Si vous avez tendance à faire souvent référence à la même requête, créer une vue correspondante est une bonne idée. Cependant, vous devez avoir l'autorisation de créer une vue dans votre base de données pour créer une vue.
• Gestion des accès. Une vue peut être utilisée pour limiter l'accès d'utilisateurs particuliers à la base de données tout en leur permettant d'obtenir les informations dont ils ont besoin. Vous pouvez donner aux utilisateurs l'accès à des vues spécifiques qui interrogent les données qu'ils sont autorisés à voir sans exposer l'ensemble de la base de données. Dans ce cas, une vue constitue une couche d'accès supplémentaire.

Pratiquons les CTE et les vues SQL !

Maintenant que vous avez une compréhension de base des CTE et des vues SQL, vous êtes prêt à commencer à les utiliser dans vos requêtes ! La pratique des requêtes SQL est la meilleure façon de comprendre le fonctionnement des CTE et de comprendre comment les vues permettent de gagner du temps sur la saisie et l'exécution des requêtes.

LearnSQL.fr a développé plusieurs cours qui couvrent ces sujets en profondeur. Tout d'abord, consultez le cours Requêtes récursives qui vous guidera des simples CTE aux CTE récursifs les plus difficiles en passant par les CTE imbriqués. 114 exercices interactifs vous aideront à maîtriser les expressions de table courantes de la manière la plus efficace possible.

Pour ceux qui souhaitent en savoir plus sur les vues SQL, nous avons préparé le cours Working with Views. Il s'agit d'un cours avancé qui vous apprendra à créer, modifier et supprimer des vues dans SQL Server, MySQL, Oracle et PostgreSQL.

Merci de votre lecture et bon apprentissage !