Cláusula With de MySQL

1. Uso de la cláusula WITH de MySQL, también conocida como expresión de tabla común
2. Uso de la cláusula WITH de MySQL que tiene varias expresiones de tabla comunes
3. Conclusión

En este tutorial, aprenderemos sobre la cláusula WITH de MySQL, también conocida como Expresión de tabla común (CTE). Los CTE se utilizan siempre que desee manipular datos de subconsultas difíciles.

También aprenderemos cómo Expresión de tabla común (CTE) le permite escribir consultas complejas de una manera fácil de leer y comprender. También veremos si podemos usar la cláusula WITH anidada o no.

Tenga en cuenta que Common Table Expression no estaba disponible antes de la versión 8.0 de MySQL. Debe tener MySQL versión 8.0 o superior para usarlo. Puede ver las novedades de la versión 8.0 de MySQL aquí.

Uso de la cláusula WITH de MySQL, también conocida como expresión de tabla común

Para usar la cláusula WITH de MySQL, entendamos primero los CTE. Las Expresiones de tabla comunes (CTE) son conjuntos de resultados temporales con nombre que solo existen en el ámbito de ejecución dentro de la declaración en la que están escritas.

Al usar la cláusula WITH, puede asignar un nombre a una subconsulta compleja que puede usar fácilmente dentro de la consulta principal (SELECT, INSERT, UPDATE o DELETE). Tenga en cuenta que todas las bases de datos no admiten la cláusula WITH.

Puede usar una o varias subconsultas y CTE dentro de la misma cláusula WITH, pero no puede usar WITH anidado (otro WITH dentro de la cláusula WITH). Vamos a crear una Table llamada tb_order y llenarla con algunos datos para practicar la cláusula WITH.

Código de ejemplo:

# 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)
);

Asegúrese en su Base de datos en Tablas de que su tabla se haya creado correctamente.

Use el siguiente comando INSERT para completar la tabla con 7 registros.

# 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');

Ahora, use el comando SELECT para ver los datos.

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

En este punto, usaremos la cláusula WITH para usar Expresión de tabla común y manipular subconsultas complejas como se indica a continuación.

# 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;

Vamos a dividir la consulta anterior en secciones para entender:

Expresión de tabla común: cte_order

Subconsulta:

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

Consulta principal:

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

Observe que el CTE se hace referencia a sí mismo en la consulta principal para leer los datos. Mostrará el siguiente resultado de acuerdo con mis datos (su resultado puede ser diferente).

Ámbito de ejecución de Expresión de tabla común

Como dijimos que CTE solo funciona dentro de su ámbito de ejecución, ¿cómo? Vea la siguiente captura de pantalla.

Cuando solo selecciona el código resaltado con el cuadro verde, permanece dentro del alcance de ejecución de CTE denominado cte_order, pero cuando solo selecciona el código dentro del cuadro rojo, ahora está fuera del alcance de ejecución y no puede hacer referencia al Expresión de tabla común denominada cte_order. Significa que puede hacer referencia al CTE dentro de la misma cláusula WITH en la que está escrito.

Uso de la cláusula WITH de MySQL que tiene varias expresiones de tabla comunes

Practiquemos la cláusula WITH usando varias expresiones comunes de tabla.

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;

Ahora, verá el siguiente resultado.

Del mismo modo, también podemos referirnos a una expresión de tabla común previamente definida desde otro CTE. Asegúrese de que ambas Expresiones de tabla comunes estén escritas dentro de la misma cláusula WITH.

Conclusión

Teniendo en cuenta la discusión anterior, hemos concluido que la cláusula WITH se usa para obtener la ventaja de la Expresión de tabla común que ayuda a manipular las subconsultas difíciles. Podemos usar varias subconsultas y expresiones de tabla comunes dentro de la misma cláusula WITH, pero no podemos tener una cláusula WITH anidada. Tampoco podemos referirnos a los CTE de distintas cláusulas WITH.