No SQL, a cláusula WITH também é conhecida como CTE. É um recurso poderoso que nos permite criar conjuntos de resultados temporários em uma consulta. Uma função importante dos CTEs é simplificar as consultas complexas em subconsultas menores e reutilizáveis. Isso ajuda a tornar o código mais legível e de fácil manutenção no longo prazo.
Junte-se a nós neste tutorial enquanto exploramos o funcionamento das Expressões de Tabela Comuns usando a cláusula WITH e a funcionalidade suportada.
Requisitos:
Para fins de demonstração, usaremos o seguinte:
- MySQL versão 8.0 e superior
- Banco de dados de amostra Sakila
Cumpridos os requisitos fornecidos, podemos prosseguir para aprender mais sobre CTEs e a cláusula WITH.
Cláusula SQL COM
A cláusula WITH nos permite definir um ou mais conjuntos de resultados temporários que são conhecidos como Common Table Expressions.
Podemos referenciar os CTEs resultantes na consulta principal como qualquer outra tabela ou conjunto de resultados. Isso desempenha um papel crucial na criação de consultas SQL modulares.
Embora a sintaxe do CTE possa variar um pouco dependendo dos seus requisitos, o seguinte mostra a sintaxe básica do CTE em SQL:
COM cte_name (coluna1, coluna2, ...) AS (
- Consulta CTE
SELECIONE...
DE ...
ONDE ...
)
-- Consulta principal
SELECIONE...
DE ...
JUNTE-SE a cte_name EM ...
ONDE ...
Começamos com a palavra-chave WITH que informa ao banco de dados SQL que desejamos criar e usar CTE.
A seguir, especificamos o nome do CTE que nos permite referenciá-lo em outras consultas.
Também especificamos uma lista opcional de nomes de colunas se o CTE incluir os aliases das colunas.
A seguir, procedemos à definição da consulta CTE. Contém todas as tarefas ou dados que o CTE realiza entre parênteses.
Por último, especificamos a consulta principal que faz referência ao CTE.
Exemplo de uso:
Uma das melhores maneiras de entender como usar e trabalhar com CTEs é observar um exemplo prático.
Tomemos por exemplo o banco de dados de amostra Sakila. Suponha que desejamos encontrar os 10 principais clientes com o maior número de aluguéis.
Dê uma olhada no CTE mostrado a seguir.
Usando a cláusula SQL WITH para encontrar os 10 principais clientes com as maiores contagens de aluguel:
COM CustomerRentals COMO (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
DO cliente c
JOIN aluguel r ON c.customer_id = r.customer_id
GRUPO POR c.customer_id, c.first_name, c.last_name
)
SELECIONE *
DE CustomerRentals
ORDER BY aluguer_count DESC
LIMITE 10;
No exemplo dado, começamos definindo um novo CTE usando a palavra-chave WITH seguida do nome que desejamos atribuir ao CTE. Neste caso, chamamos de “CustomerRentals”.
Dentro do corpo do CTE calculamos a contagem de aluguéis de cada cliente juntando a tabela de clientes e aluguéis.
Por último, na consulta principal, selecionamos todas as colunas do CTE, ordenamos os resultados com base na contagem de aluguéis (ordem decrescente) e limitamos a saída apenas às 10 primeiras linhas.
Isso nos permite buscar os clientes com o maior número de aluguéis, conforme mostrado no resultado a seguir:
CTEs recursivos
Em alguns outros casos, você pode estar lidando com estruturas de dados hierárquicas. É aqui que os CTEs recursivos entram em ação.
Tomemos por exemplo um caso em que queremos navegar na organização hierárquica ou representar uma estrutura semelhante a uma árvore. Podemos usar a palavra-chave WITH RECURSIVE para criar um CTE recursivo.
Como não há dados hierárquicos que possamos usar no banco de dados Sakila para demonstrar um CTE recursivo, vamos dar um exemplo básico.
Departamento CREATE TABLE (departamento_id INT CHAVE PRIMÁRIA AUTO_INCREMENT,
nome_do_departamento VARCHAR(255) NÃO NULO,
parent_department_id INT,
CHAVE ESTRANGEIRA (parent_department_id) REFERÊNCIAS departamento (department_id)
);
INSERT INTO departamento (department_name, parent_department_id)
VALORES
('Corporativo', NULO),
('Finanças', 1),
('RH', 1),
('Contabilidade', 2),
'Recrutamento', 3),
('Folha de pagamento', 4);
Neste caso, temos um exemplo de tabela de “departamento” com alguns dados aleatórios. Para encontrar a estrutura hierárquica dos departamentos, podemos utilizar um CTE recursivo da seguinte forma:
COM Departamento RECURSIVOHierarquia AS (SELECIONE ID_do_departamento, nome_do_departamento, ID_do_departamento_pai
DO departamento
ONDE parent_department_id É NULO
UNIÃO TODOS
SELECIONE d.department_id, d.department_name, d.parent_department_id
DO departamento d
JUNTE-SE ao DepartamentoHierarquia dh ON d.parent_department_id = dh.department_id
)
SELECIONE *
DO DepartamentoHierarquia;
Nesse caso, o CTE recursivo começa com departamentos com um “parent_department_id” NULL (departamentos raiz) e recupera recursivamente os departamentos filhos.
Conclusão
Neste tutorial, aprendemos sobre os recursos mais fundamentais e úteis em bancos de dados SQL, como Common Table Expressions, entendendo como trabalhar com a palavra-chave WITH.