Curso de MariaDB / Dividir consultas com WITH e expressões de tabela comuns
Uma expressão de tabela comum, ou CTE, do inglês Common Table Expression, usa WITH para dar nome a um resultado intermediário dentro de uma instrução SQL. Ela permite separar filtros e agregações para compreender a consulta, mas não cria uma tabela permanente nem garante uma execução mais rápida.
Dar uma função clara a cada resultado intermediário
Selecionar pedidos pagos, somar itens e acrescentar o nome do cliente em uma expressão muito aninhada pode dificultar a revisão. Nesta prática, paid_orders identifica os pedidos que atendem à condição e order_totals calcula um valor de mercadorias por pedido. Defina, para cada nome, o significado de uma linha e as colunas que permitem relacioná-la às demais.
| Resultado | O que uma linha representa | Colunas principais |
|---|---|---|
| paid_orders | Um pedido pago | order_id, customer_id |
| order_totals | O total de mercadorias de um pedido | order_id, goods_total |
| SELECT final | Um pedido que atende à condição e tem itens | Pedido, nome do cliente e valor das mercadorias |
Os nomes ajudam a explicar a lógica, mas não alteram sozinhos a quantidade de linhas de uma relação. A explicação dos totais duplicados em uniões mostra por que é necessário definir essa unidade antes de somar valores.
Preparar um conjunto independente de pedidos
Conecte ao servidor de prática como course_user e selecione shopcourse. Você pode salvar os blocos em um arquivo UTF-8 e executá-lo dentro do cliente com SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. O guia do cliente MariaDB e dos arquivos SQL detalha conexão e execução. Introduza o SQL dentro do cliente MariaDB.
Confira SELECT VERSION(), DATABASE(); e SHOW TABLES; antes de criar c31_customers, c31_products, c31_orders e c31_items. Os quatro nomes pertencem exclusivamente a este exercício. Se já existirem, examine definições e registros antes de decidir se pode removê-los; preserve objetos de aplicações. Confira na documentação do servidor se sua versão aceita WITH.
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);
O SQL mantém os nomes e cidades originais em coreano. Os produtos 1–4 são caderno, caneta, caneca e luminária. Os clientes 1 e 3 têm o valor de Seul; o 2, o de Busan; e o 4, o de Daejeon. Conserve esses literais UTF-8 ao reproduzir a prática; as explicações usam identificadores para acompanhar os resultados.
Os pedidos 101, 102 e 103 estão pagos (paid); o 104 está cancelado (canceled). O total de mercadorias usa quantidade e preço armazenados em cada item, sem acrescentar frete nem substituir o preço histórico pelo preço atual de catálogo.
Definir várias CTEs antes do 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;
Escreva WITH uma vez e separe as definições por vírgulas. Cada nome é seguido de AS e do SELECT entre parênteses; depois da última definição, escreva o SELECT que usa os resultados. O ponto e vírgula fica ao final da instrução inteira: colocá-lo depois de uma definição intermediária encerra a instrução antes de usá-la.
| Pedido | ID do cliente | Total de mercadorias |
|---|---|---|
| 101 | 1 | 11500.00 |
| 102 | 1 | 8000.00 |
| 103 | 2 | 19500.00 |
O SELECT real mostra customer_name, o nome coreano armazenado; a tabela explicativa usa o ID para identificar o cliente. O cliente 1 aparece em duas linhas porque tem dois pedidos pagos. O resultado contém uma linha por pedido, não uma por cliente.
Examinar as linhas de cada etapa
Se um valor não coincidir, execute separadamente o SELECT interno de cada CTE. paid_orders precisa conter três pedidos: 101, 102 e 103. order_totals agrupa os itens dos quatro pedidos, inclusive o cancelado 104, cujo total é 1500.00. A união final com paid_orders exclui esse pedido do 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 bloco conserva a definição de paid_orders e usa um SELECT final simples para examiná-la. Aplique o mesmo método a outra etapa: mantenha sua definição e selecione suas colunas. Depois, volte ao SELECT completo e confira se o resultado original se reproduz.
O JOIN interno com order_totals exige itens no pedido. Um pedido pago sem itens apareceria em paid_orders, mas não no SELECT final deste exemplo. Se o relatório precisar incluí-lo com valor zero, use uma união externa e um tratamento explícito do valor ausente. Da mesma forma, obter uma linha por cliente exige outra agregação; mudar o nome de uma CTE não faz essa operação.
Compreender o alcance e as decisões de execução
O nome de uma CTE existe somente na instrução que contém seu WITH. Um segundo SELECT independente não pode reutilizar paid_orders sem defini-lo novamente. Não aparece uma tabela permanente para remover ao terminar. Para reutilizar uma definição em várias instruções, avalie uma view; para armazenar linhas temporárias, considere uma tabela temporária adequada ao trabalho.
Os nomes em uma cláusula WITH precisam ser distintos. Uma CTE não recursiva pode usar outra definida antes na mesma cláusula. A recursão, que permite percorrer hierarquias por referências recursivas, é um tema diferente da divisão não recursiva apresentada aqui.
WITH não implica que um resultado seja calculado exatamente uma vez ou que a consulta fique mais rápida. A combinação ou materialização de resultados intermediários depende do otimizador, da consulta e da versão. Separe a melhoria de legibilidade da avaliação de desempenho e use EXPLAIN para analisar o plano quando necessário.
A referência oficial de WITH no MariaDB documenta sintaxe, alcance e referências entre CTEs.
Diagnosticar erros e limpar as tabelas de prática
Em erros de sintaxe, confira parênteses, vírgulas e ponto e vírgula final. Se faltar uma coluna, examine a lista SELECT da CTE que deveria fornecê-la. Prefira colunas explícitas e nomes que não ocultem desnecessariamente tabelas existentes. Em totais incorretos, confira primeiro as chaves da união e o significado de cada linha, em vez de acrescentar DISTINCT para esconder repetições.
Antes de repetir a preparação, examine banco selecionado, definições e dados; copie o que precisar conservar. Não existem objetos CTE permanentes para excluir. Se as quatro tabelas seguintes forem somente desta prática, remova nesta ordem, primeiro as filhas e depois as referenciadas. DROP TABLE remove estrutura e registros e não é desfeito por um ROLLBACK comum; separe a limpeza dos testes de transações.
DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;




