Curso de MariaDB / Dividir consultas con WITH y expresiones de tabla comunes
Una expresión de tabla común, o CTE por sus siglas en inglés (Common Table Expression), asigna con WITH un nombre a un resultado intermedio dentro de una sentencia SQL. Permite separar filtros y agregaciones para entender la consulta, pero no crea una tabla permanente ni garantiza una ejecución más rápida.
Dar una función clara a cada resultado intermedio
Seleccionar pedidos pagados, sumar sus líneas y añadir el nombre del cliente en una expresión muy anidada puede dificultar la revisión. En este ejercicio, paid_orders identifica los pedidos que cumplen la condición y order_totals calcula un importe de productos por pedido. Defina para cada nombre qué representa una fila y qué columnas permiten relacionarla con las demás.
| Resultado | Qué representa una fila | Columnas principales |
|---|---|---|
| paid_orders | Un pedido pagado | order_id, customer_id |
| order_totals | El total de productos de un pedido | order_id, goods_total |
| SELECT final | Un pedido que cumple la condición y tiene líneas | Pedido, nombre del cliente e importe de productos |
Los nombres ayudan a explicar la lógica; no cambian por sí solos cuántas filas devuelve una relación. La explicación de los totales duplicados al unir tablas muestra por qué conviene fijar esa unidad antes de agregar importes.
Preparar un juego de pedidos independiente
Conéctese al servidor de prácticas como course_user y seleccione shopcourse. Puede guardar los bloques en un archivo UTF-8 y ejecutarlo dentro del cliente mediante SOURCE C:/mariadb-lab/lesson.sql;, cambiando la ruta por la real. La guía del cliente de MariaDB y los archivos SQL detalla la conexión y la ejecución. El SQL se introduce dentro del cliente, no en la consola del sistema operativo.
Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c31_customers, c31_products, c31_orders y c31_items. Los cuatro nombres pertenecen exclusivamente a este ejercicio. Si ya existen, revise sus definiciones y registros antes de decidir si puede eliminarlos; no sustituya objetos de una aplicación. Verifique que su versión admite WITH con la documentación de su servidor.
USE shopcourse;
CREATE TABLE c31_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c31_order_customer FOREIGN KEY (customer_id)
REFERENCES c31_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c31_item_order FOREIGN KEY (order_id)
REFERENCES c31_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c31_item_product FOREIGN KEY (product_id)
REFERENCES c31_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c31_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c31_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c31_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c31_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
El SQL conserva los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 tienen el valor de Seúl; el 2, el de Busan, y el 4, el de Daejeon. Mantenga estos literales UTF-8 al reproducir el ejercicio; las explicaciones utilizan sus identificadores para seguir los resultados.
Los pedidos 101, 102 y 103 están pagados (paid); el 104 está cancelado (canceled). El total de productos se calcula con la cantidad y el precio guardado en cada línea, sin añadir el envío ni sustituir ese precio histórico por el precio actual del catálogo.
Definir varias CTE antes del SELECT final
WITH paid_orders AS (
SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;
Escriba WITH una sola vez y separe las definiciones con comas. Cada nombre va seguido de AS y de su SELECT entre paréntesis; después de cerrar la última definición se escribe el SELECT que consume los resultados. El punto y coma aparece al final de toda la sentencia: colocarlo después de una definición intermedia termina la sentencia antes de que pueda utilizarla.
| Pedido | ID del cliente | Total de productos |
|---|---|---|
| 101 | 1 | 11500.00 |
| 102 | 1 | 8000.00 |
| 103 | 2 | 19500.00 |
El SELECT real muestra customer_name, el nombre coreano almacenado; la tabla explicativa usa el ID para identificar al cliente. El cliente 1 aparece en dos filas porque tiene dos pedidos pagados. El resultado tiene una fila por pedido, no una por cliente.
Revisar las filas de cada etapa
Si un importe no coincide, ejecute por separado el SELECT interior de cada CTE. paid_orders debe contener tres pedidos: 101, 102 y 103. order_totals agrupa las líneas de los cuatro pedidos, incluido el cancelado 104, cuyo total es 1500.00. Es la unión final con paid_orders la que excluye ese pedido del resultado mostrado.
WITH paid_orders AS (
SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;
Este bloque conserva la definición de paid_orders y utiliza un SELECT final sencillo para inspeccionarla. Puede aplicar el mismo método a otra etapa: mantenga su definición y seleccione sus columnas. Vuelva después al SELECT completo para comprobar que el resultado original se reproduce.
El JOIN interno con order_totals exige que el pedido tenga líneas. Si se añadiera un pedido pagado sin líneas, aparecería en paid_orders, pero no en el SELECT final de este ejemplo. Si su informe debe incluirlo con un importe cero, necesitaría una unión externa y un tratamiento explícito del importe ausente. Del mismo modo, obtener una fila por cliente exige otra agregación; cambiar el nombre de una CTE no la realiza.
Comprender el alcance y las decisiones de ejecución
El nombre de una CTE solo existe en la sentencia que incluye su WITH. Un segundo SELECT independiente no puede reutilizar paid_orders sin volver a definirlo. No aparece una tabla permanente que deba borrar al terminar. Para reutilizar una definición en varias sentencias, valore una vista; si necesita almacenar filas temporales, estudie una tabla temporal adecuada al ciclo de trabajo.
En una cláusula WITH, los nombres deben ser distintos. Una CTE no recursiva puede utilizar otra definida antes en la misma cláusula. La recursión, que permite recorrer jerarquías mediante referencias recursivas, es un tema diferente del desglose no recursivo mostrado aquí.
WITH no implica que un resultado se calcule exactamente una vez ni que la consulta sea más rápida. Las decisiones de combinación o materialización de resultados intermedios dependen del optimizador, de la consulta y de la versión. Separe la mejora de legibilidad de la evaluación del rendimiento y utilice EXPLAIN cuando necesite analizar el plan.
La referencia oficial de WITH en MariaDB documenta la sintaxis, el alcance y las referencias entre CTE.
Diagnosticar errores y limpiar las tablas de prácticas
Ante un error de sintaxis, revise paréntesis, comas y el punto y coma final. Si falta una columna, compruebe la lista SELECT de la CTE que debería proporcionarla. Prefiera columnas explícitas y nombres que no oculten innecesariamente tablas existentes. Ante totales incorrectos, compruebe primero las claves de unión y qué representa cada fila, en lugar de añadir DISTINCT para ocultar repeticiones.
Antes de repetir la preparación, revise la base seleccionada, las definiciones y los datos; respalde lo que deba conservar. No hay objetos CTE permanentes que eliminar. Si las cuatro tablas siguientes son solo de este ejercicio, puede borrarlas en este orden, primero las hijas y después las referenciadas. DROP TABLE elimina estructuras y registros y no se revierte con un ROLLBACK ordinario; separe esta limpieza de las pruebas de transacciones.
DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;




