Peut-on utiliser plusieurs instructions WITH en SQL ?

Un guide complet sur les instructions multiples WITH en SQL, parfait pour les débutants comme pour les experts.

La clause SQL WITH vous permet de définir une CTE (common table expression). Une CTE est comme une table qui est remplie pendant l'exécution de la requête. Vous pouvez utiliser plusieurs instructions WITH dans une requête SQL pour définir plusieurs CTE. Dans cet article, nous expliquerons comment définir plusieurs CTE dans une seule requête. Nous montrerons également comment cette approche permet d'augmenter la puissance d'expression d'une requête SQL.

Peut-on utiliser plusieurs instructions WITH en SQL ?

Oui ! Vous trouverez ci-dessous une requête simple utilisant deux clauses WITH :

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

Dans la requête ci-dessus, il y a deux CTE. Le premier (en rouge) s'appelle wine_exporting_country et le second (en bleu) car_exporting_country. La requête principale (en noir) fait référence aux deux déclarations WITH en tant que tables régulières à l'aide de INTERSECT. L'intersection des deux tables ne renvoie que les pays présents dans les deux résultats de l'ETC.

Notez que le mot-clé WITH n'apparaît qu'une seule fois. Après la clause WITH, vous devez indiquer le nom de l'ETC, puis le mot-clé AS et enfin, entre parenthèses, la requête SELECT pour définir l'ETC.

Si vous souhaitez apprendre à utiliser plusieurs instructions WITH dans une requête, je vous recommande notre cours interactif Requêtes récursives cours interactif. Il vous enseigne les détails de la syntaxe de la clause WITH dans plus de 100 exercices pratiques. Il couvre la syntaxe de base, les instructions WITH multiples et les requêtes récursives - l'utilisation la plus complexe de la syntaxe WITH.

La clause WITH en SQL

Expliquons d'abord comment fonctionne la clause WITH. Elle crée une sorte de table virtuelle (l'ETC) à la volée, qui est créée et alimentée pendant l'exécution d'une seule instruction SQL ; après l'exécution de l'instruction SQL, la "table" de l'ETC est automatiquement supprimée. Pour une meilleure compréhension de la clause WITH, je vous suggère l'article Qu'est-ce qu'un CTE?

Dans cet article, nous utiliserons une table de base de données appelée product qui suit les pays et les produits les plus populaires qu'ils produisent (par exemple, l'huile d'olive, le vin, les voitures). Elle enregistre les quantités produites, importées et exportées par chaque pays. La table contient également le prix du produit et la population du pays.

Voyons un exemple simple de requête qui utilise une clause WITH pour définir un CTE. Supposons que nous voulions obtenir la liste des pays qui exportent plus d'huile d'olive qu'ils n'en importent. Nous voulons que les résultats soient ordonnés en fonction de la quantité produite par chaque pays. Voici la requête :

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

Dans cette requête, nous avons un CTE appelé olive_oil_exporter, qui est comme une table avec deux colonnes : country et qty_produced. Les lignes de l'ETC ne contiennent que les pays qui exportent plus d'huile d'olive qu'ils n'en importent. Ensuite, nous utilisons une requête ordinaire SELECT pour demander à l'ETC olive_oil_exporter les noms des pays, que nous classons par ordre décroissant en fonction de la quantité d'huile d'olive produite dans le pays en question.

Utilisation de plusieurs déclarations WITH en SQL

Supposons que nous souhaitions classer les pays en deux catégories : les pays producteurs de denrées alimentaires et les pays producteurs de technologies. Pour être un producteur alimentaire, un pays doit exporter plus de 100 millions de dollars de produits alimentaires. Pour être un producteur de haute technologie, un pays doit produire plus de 1000 dollars de produits technologiques par habitant.

Nous voulons un rapport contenant les noms de tous les pays et deux colonnes appelées is_a_food_producer et is_a_hightech_producer. La requête SQL pour ce rapport est la suivante :

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

Dans la requête ci-dessus, nous pouvons clairement identifier trois requêtes distinctes. Les deux premières requêtes utilisent la clause WITH pour définir deux tables CTE : food_producer et hightech_producer. La troisième requête est la requête principale, qui consomme les deux tables CTE créées précédemment.

Après la clause WITH, vous pouvez voir le nom de la table CTE (food_producer), puis la sous-clause AS et enfin (entre parenthèses) la requête pour cette CTE. Pour le deuxième ETC, la clause WITH n'est pas nécessaire ; il suffit de mettre une virgule, puis de répéter la même syntaxe en commençant par le nom de l'ETC.

Les deux ETC food_producer et hightech_producer n'ont qu'une seule colonne : country. La requête principale extrait les noms de tous les pays de la table productpuis une requête LEFT JOIN est effectuée pour chacun des CTE. Lorsque LEFT JOIN n'a pas de ligne correspondante, cela signifie que la réponse pour ce pays est "Non" ; lorsqu'il y a une ligne correspondante, la valeur pour ce pays est "Oui".

Avant de passer à la section suivante, j'aimerais vous suggérer l'article Comment écrire plusieurs ETC. Vous y trouverez de nombreux exemples et explications sur les CTE.

Utilisation d'instructions WITH multiples et imbriquées en SQL

Dans certains cas, nous avons besoin d'un deuxième ETC basé sur le premier : un ETC imbriqué. En d'autres termes, la requête définissant le second CTE doit faire référence au premier CTE. Voyons un exemple.

Nous voulons obtenir le montant total en dollars des exportations de produits alimentaires par pays. Dans le même rapport, nous voulons montrer le pourcentage que ce montant représente dans les exportations totales de ce pays pour tous les types de produits. La requête est la suivante :

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

Dans cette requête, nous avons créé un CTE appelé country_export_by_product qui possède les colonnes country, product_family et total_exports (qui représente le montant total de ce produit exporté par ce pays (en dollars)). Notez que la clause GROUP BY utilise les colonnes country et product_family.

L'ETC suivant s'appelle country_export_total et est basé sur l'ETC précédent country_export_by_product. L'idée de cette ETC est d'obtenir le montant total exporté par chaque pays sur la base de l'ETC précédente. Notez que dans la deuxième ETC, nous utilisons une clause de pays GROUP BY. C'est parce que nous avons besoin de différents niveaux de clause GROUP BY que nous avons deux CTE.

La requête principale fait référence aux deux CTE, en les reliant par la valeur du pays. Ensuite, l'expression

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

... est utilisée pour calculer le pourcentage que chaque produit représente dans les exportations totales de ce pays.

En termes de syntaxe, vous pouvez faire en sorte qu'un CTE fasse référence à un autre CTE dans la même requête. C'est ce que nous avons fait dans notre requête : lors de la définition de CTE country_export_total, nous avons fait référence au CTE country_export_by_product défini précédemment.

Notez que nous pouvons faire référence à l'instruction WITH définie avant l'instruction WITH actuelle, mais pas à celles qui la suivent. Vous pouvez faire référence à chaque instruction WITH plusieurs fois dans une autre instruction WITH ou dans une requête principale. Dans notre exemple, nous avons fait référence à la première définition de WITH (l'ETC country_export_by_product ) à deux endroits : Dans la seconde WITH (l'ETC country_export_total ) et dans la requête principale.

Les autres limitations liées à la syntaxe de la clause WITH sont les suivantes :

• Vous ne devez utiliser le mot-clé WITH qu'une seule fois, avant le premier ETC.
• Tous les CTE sont séparés par des virgules, mais il n'y a pas de virgule avant la requête principale. Ceci est conforme au modèle de syntaxe :

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

Je suggère l'article Les CTE SQL expliqués avec des exemples pour plusieurs autres exemples de requêtes WITH; ils démontrent comment améliorer l'organisation et la lisibilité de vos requêtes SQL en utilisant des CTEs.

Utilisation de la clause WITH pour créer Requêtes récursives

La clause WITH de SQL permet de définir des requêtes récursives. Les requêtes récursives vous permettent d'interroger des structures hiérarchiques (c'est-à-dire des organigrammes, des arbres ou des graphiques). Pour en savoir plus sur l'interrogation des structures hiérarchiques, cliquez ici.

Les requêtes récursives sont basées sur la clause WITH. Pour créer une requête récursive, vous n'avez besoin que d'une clause WITH, mais la requête contenue dans WITH se compose de deux parties.

Les requêtes récursives sont utiles lorsque les tables ou le modèle de données de la base de données présentent une sorte de hiérarchie implicite. L'exemple de table le plus courant pour expliquer ce sujet est sans doute la table typique employee avec les colonnes employee_id et manager_employee_id.

Si nous voulons qu'un rapport affiche tous les employés avec les noms de leurs responsables et le niveau hiérarchique de l'employé, nous pouvons utiliser la requête récursive suivante :

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

Dans cette requête, nous pouvons voir la clause WITH RECURSIVE, qui est utilisée pour créer un CTE récursif appelé company_hierarchy. L'ETC contiendra tous les noms des employés avec les noms de leurs responsables. Notez que l'ETC comporte deux instructions SELECT reliées par UNION ALL. La première SELECT permet d'obtenir le premier employé de la requête récursive (le PDG John Smith).

Le second SELECT de UNION est une requête qui est exécutée plusieurs fois. À chaque exécution, elle renvoie le(s) employé(s) du niveau suivant dans la hiérarchie. Par exemple, sa première exécution renvoie tous les employés qui dépendent directement de Jean Dupont.

Enfin, il existe une troisième instruction SELECT; elle se situe en dehors de l'ETC. Elle sélectionne les noms des employés, les noms de leurs supérieurs et le niveau hiérarchique. Les données sont extraites de l'ETC et jointes à la table employees. Nous utilisons LEFT JOIN parce que nous voulons toutes les données de l'ETC (y compris John Smith, qui a une valeur NULL dans la colonne manager_id). Les résultats sont affichés par ordre croissant : d'abord par niveau hiérarchique, puis par employee_id du patron. Vous trouverez ci-dessous le résultat de la requête :

Pour en savoir plus sur les requêtes récursives, consultez l'article Qu'est-ce qu'une ETC récursive en SQL ?

Poursuivre l'apprentissage des déclarations WITH multiples en SQL

Dans cet article, nous avons abordé l'utilisation de plusieurs instructions WITH dans une seule requête SQL. Nous avons également mentionné comment utiliser la clause WITH dans les requêtes récursives. Si vous souhaitez poursuivre votre apprentissage de la clause WITH, je vous recommande notre cours Requêtes récursives qui offre une excellente opportunité de pratiquer le type de requêtes SQL le plus difficile.

Les instructions WITH multiples sont particulièrement utiles lorsque vous écrivez des rapports SQL complexes. Si c'est votre cas, je vous recommande également notre antisèche gratuite SQL pour l'analyse de données, que nous avons spécialement conçue pour vous aider à écrire des requêtes complexes pour l'analyse de données.

Si vous souhaitez pratiquer SQL à un niveau avancé, consultez notre piste de pratique SQL avancé . Elle contient plus de 200 exercices pour vous aider à pratiquer les concepts SQL avancés. Tous les deux mois, nous publions un nouveau cours de pratique SQL avancé dans notre piste mensuelle La pratique du SQL.

Vous pouvez également obtenir tous ces cours et plus encore dans notre Tout à vie plan. Ce plan vous donne un accès à vie à tous nos cours SQL à différents niveaux de compétence et dans quatre dialectes SQL. Inscrivez-vous dès aujourd'hui !