MySQL With clause

1. Utilisation de la clause MySQL WITH alias Common Table Expression
2. Utilisation de la clause MySQL WITH ayant plusieurs Common Table Expressions
3. Conclusion

Dans ce tutoriel, nous allons découvrir la clause WITH de MySQL, également connue sous le nom de Common Table Expression (CTE). Les CTE sont utilisés chaque fois que vous souhaitez manipuler des données de sous-requêtes difficiles.

Nous apprendrons également comment Common Table Expression (CTE) vous permet d’écrire des requêtes complexes de manière facilement lisible et compréhensible. Nous verrons également si nous pouvons utiliser ou non la clause WITH imbriquée.

Veuillez noter que Common Table Expression n’était pas disponible avant la version 8.0 de MySQL. Vous devez avoir MySQL version 8.0 ou supérieure pour l’utiliser. Vous pouvez voir les nouveautés de MySQL version 8.0 ici.

Utilisation de la clause MySQL WITH alias Common Table Expression

Pour utiliser la clause WITH de MySQL, comprenons d’abord les CTE. Les expressions de table communes (CTE) sont des ensembles de résultats temporaires nommés qui n’existent que dans la portée d’exécution de l’instruction dans laquelle ils sont écrits.

En utilisant la clause WITH, vous pouvez attribuer un nom à une sous-requête complexe que vous pouvez facilement utiliser au sein de la requête principale (SELECT, INSERT, UPDATE ou DELETE). Gardez à l’esprit que toutes les bases de données ne prennent pas en charge la clause WITH.

Vous pouvez utiliser une ou plusieurs sous-requêtes et CTE dans la même clause WITH, mais vous ne pouvez pas utiliser WITH imbriqué (un autre WITH à l’intérieur de la clause WITH). Créons une Table nommée tb_order et remplissons-la avec des données pour pratiquer la clause WITH.

Exemple de code :

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `practice_with_clause`.`tb_order` (
ORDER_ID INTEGER NOT NULL,
CUSTOMER_FIRST_NAME	VARCHAR(30) NOT NULL,
CUSTOMER_LAST_NAME VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
PURCHASED_PRODUCTS VARCHAR(64) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY (ORDER_ID)
);

Assurez-vous dans votre Base de données sous Tables que votre table est créée avec succès.

Utilisez la commande INSERT suivante pour remplir la table avec 7 enregistrements.

# SQL Programming Using MySQL Version 8.27
INSERT INTO practice_with_clause.tb_order 
(ORDER_ID, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CITY_NAME, PURCHASED_PRODUCTS, ORDER_DATE)
VALUES
(1,'John','Horton', 'Washington', 'Books', '2021-05-03'),
(2,'Banji','Horton',  'Florida', 'Pens', '2010-5-6'),
(3,'Nayya','Sofia',  'South Carolina', 'Books', '2011-10-15'),
(4,'Martell','Daniel',  'Michigan', 'NoteBooks', '2012-12-02'),
(5,'Sana','Preston',  'Michigan', 'White Board Marker', '2013-08-27'),
(6,'Gulraiz','Yonja', 'Washington', 'Books', '2021-05-03'),
(7,'Mashal','Naaz',  'Florida', 'Comic Books', '2019-01-01');

Maintenant, utilisez la commande SELECT pour afficher les données.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM practice_with_clause.tb_order;

À ce stade, nous allons utiliser la clause WITH pour utiliser l’expression de table commune et manipuler la sous-requête complexe comme indiqué ci-dessous.

# SQL Programming Using MySQL Version 8.27
WITH cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM cte_order;

Décomposons la requête ci-dessus en sections pour comprendre :

Expression de table commune : cte_order

Sous-requête :

SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders FROM practice_with_clause.tb_order GROUP BY PURCHASED_PRODUCTS

Requête principale :

SELECT AVG(Number_of_Orders) AS "Average Orders Per Category" FROM cte_order;

Notez que le CTE se référence lui-même dans la requête principale pour lire les données. Il affichera la sortie suivante en fonction de mes données (votre sortie peut être différente).

Portée d’exécution de Common Table Expression

Comme nous l’avons dit, le CTE ne fonctionne que dans son périmètre d’exécution, comment ? Voir la capture d’écran suivante.

Lorsque vous ne sélectionnez que le code mis en surbrillance avec la case verte, vous restez dans le périmètre d’exécution de CTE nommé cte_order, mais lorsque vous sélectionnez uniquement le code dans la zone rouge, vous êtes maintenant hors de la portée d’exécution et ne pouvez pas référencer le Expression de table commune nommée cte_order. Cela signifie que vous pouvez référencer le CTE dans la même clause WITH dans laquelle il est écrit.

Utilisation de la clause MySQL WITH ayant plusieurs Common Table Expressions

Pratiquons la clause WITH en utilisant plusieurs expressions de table communes.

WITH 
cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
),
cte_location AS
(
SELECT COUNT(CITY_NAME) as City
FROM practice_with_clause.tb_order
WHERE CITY_NAME = 'Washington'
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category", City
FROM cte_order,cte_location;

Maintenant, vous verrez la sortie suivante.

De même, nous pouvons également faire référence à une expression de table commune précédemment définie à partir d’un autre CTE. Assurez-vous que les deux expressions de table communes sont écrites dans la même clause WITH.

Conclusion

Compte tenu de la discussion ci-dessus, nous avons conclu que la clause WITH est utilisée pour obtenir l’avantage de l'expression de table commune qui aide à manipuler les sous-requêtes difficiles. Nous pouvons utiliser plusieurs sous-requêtes et expressions de table communes dans la même clause WITH, mais nous ne pouvons pas avoir de clause WITH imbriquée. Nous ne pouvons pas non plus faire référence aux CTE à partir de différentes clauses WITH.