Qual é a diferença entre os CTEs e Views SQL?

Visualizações SQL vs. CTEs: O que eles fazem? Como elas são diferentes? Qual delas deve ser usada e quando? Obtenha suas respostas aqui!

Em SQL, tanto os CTEs (expressões comuns de tabelas) quanto as visualizações ajudam a organizar suas consultas, levando a um código mais limpo e fácil de seguir. No entanto, existem algumas diferenças importantes entre elas. Este artigo irá guiá-lo através de vários exemplos de CTEs e visões e explicar quando usar cada um deles.

O que são Expressões de Tabela Comum (CTEs) em SQL?

Como você deve se lembrar, expressões de tabelas comuns são nomeadas conjuntos de resultados temporários que são criados usando instruções SQL simples e depois referenciadas dentro das instruções SELECT, INSERT, UPDATE, ou DELETE.

Por exemplo, digamos que temos uma tabela chamada top_apps com informações de classificação para as principais aplicações em diferentes categorias:

Também temos o google_apps tabela, que tem as mesmas informações para várias aplicações do Google:

Queremos comparar o desempenho dos aplicativos do Google com o dos aplicativos de melhor desempenho nas categorias correspondentes. Especificamente, queremos que as informações sobre a classificação máxima em cada categoria sejam mostradas ao lado da classificação do aplicativo Google da mesma categoria.

Aqui está uma consulta que atinge este objetivo usando um CTE:

WITH top_apps_max AS (
	SELECT category, MAX(rating) AS max_rating
	FROM top_apps
	GROUP BY category)
SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

A consulta começa com a criação de um conjunto de resultados temporários chamado top_apps_max. Este conjunto de resultados é derivado do top_apps tabela e inclui a lista de categorias juntamente com as classificações máximas correspondentes. Em seguida, na declaração principal SELECT, juntamos este conjunto de resultados temporários com o google_apps tabela usando a coluna comum category. Aqui está o resultado:

Você poderia obter o mesmo resultado usando uma subconsulta em vez de um CTE. Entretanto, em comparação às subconsultas, o uso de um CTE SQL resulta em um código mais limpo e fácil de seguir que você pode ler de cima para baixo: primeiro você cria um conjunto de resultados temporários com um nome específico que é usado posteriormente na consulta para referenciar esse conjunto de resultados.

Observe que o CTE só existe na memória enquanto a consulta está em execução. Após a consulta ser executada, o CTE é descartado; ele não pode ser usado para a próxima consulta SQL, a menos que a definamos novamente. Ainda assim, o mesmo CTE pode ser referenciado várias vezes na consulta principal e em quaisquer subconsultas.

Você pode aprender mais sobre os CTEs SQL neste guia introdutório abrangente de expressões comuns de tabelas. E se você estiver interessado em praticar CTEs com exemplos do mundo real, verifique nosso curso interativo sobre expressões comuns de tabelas.

No discurso cotidiano, os CTEs são às vezes chamados de visualizações em linha. Portanto, vamos lembrar o que é uma visão e como ela difere de um CTE.

O que é uma visão em SQL?

Uma view é uma consulta SQL armazenada que é executada cada vez que você faz referência a ela em outra consulta. Note que uma view não armazena a saída de uma determinada consulta - ela armazena a própria consulta.

Vamos ver como isto funciona. Vamos usar um exemplo semelhante, mas desta vez usaremos uma view em vez de um CTE.

Começaremos criando a view top_apps_max com a palavra-chave CREATE VIEW, seguida da declaração SELECT:

CREATE VIEW top_apps_max AS
SELECT category, MAX(rating) AS max_rating, MAX(reviews) AS max_num_reviews
FROM top_apps
GROUP BY category;

Como você vê, a declaração SELECT é muito parecida com a que usamos com o CTE. Poderia ser absolutamente a mesma, mas mudamos o escopo: Agora queremos ver o número máximo de revisões além da classificação máxima para cada categoria (apenas para ter mais dados para trabalhar).

Portanto, agora temos uma consulta SQL armazenada chamada top_apps_max. Chegou a hora de usá-la!

Vamos começar replicando nosso primeiro exemplo usando uma vista em vez de um CTE. Entretanto, desta vez:

• Não precisamos criar um CTE no início da consulta, pois já temos a visualização armazenada top_apps_max.
• Nós simplesmente nos juntamos ao google_apps tabela com a visão top_apps_max na coluna da categoria e listar as colunas que queremos ver na saída:

SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

O resultado desta consulta será o mesmo que o do nosso primeiro exemplo:

Então, qual é a diferença?

Primeiro de tudo, você pode usar a mesma visão em outras consultas sem defini-la novamente. Por exemplo, a consulta SQL abaixo faz referência à mesma visualização top_apps_max; desta vez, ela é usada para comparar o número de revisões ao invés da classificação de diferentes aplicações:

SELECT ga.name, ga.category, ga.reviews, tam.max_num_reviews
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Além disso, você pode usar um CTE ao definir uma visualização. Digamos que também queremos ver o nome do aplicativo top em cada categoria (ou seja, o aplicativo com o maior número de revisões). Uma maneira de fazermos isso é criar uma visão que produza o nome, categoria e número de revisões para a aplicação superior em cada categoria:

CREATE VIEW top_app_per_category AS
WITH top_app_max_reviews AS (
    SELECT category, MAX(reviews) AS max_num_reviews
    FROM top_apps
    GROUP BY category)
SELECT ta.name, ta.category, ta.reviews
FROM top_apps ta
JOIN top_app_max_reviews tamr
ON ta.reviews = tamr.max_num_reviews;

Como você pode ver, a consulta armazenada nesta visão inclui uma expressão de tabela comum que produz o número máximo de revisões para cada categoria. Então, na consulta principal da visualização top_app_per_category, juntamos este CTE com a tabela top_apps para obter o nome da aplicação com o maior número de revisões em cada categoria.

Agora podemos fazer referência a esta visão em outra consulta que retorna as aplicações do Google junto com o nome da aplicação de topo na categoria correspondente e seu número de revisões:

SELECT ga.name, ga.category, ga.reviews, top.name AS top_app, top.reviews AS top_app_reviews
FROM google_apps ga
JOIN top_app_per_category top
ON ga.category = top.category;

O fundamental a lembrar sobre as visualizações SQL é que, ao contrário de um CTE, uma visualização é um objeto físico em um banco de dados e é armazenada em um disco. Entretanto, as visualizações armazenam apenas a consulta, não os dados retornados pela consulta. Os dados são computados cada vez que você faz referência à visualização em sua consulta.

Interessado em saber mais sobre as visualizações SQL? Aqui está um ótimo artigo que explica as visualizações SQL usando múltiplos exemplos e ilustrações. Lembre-se também de praticar as views com nosso curso interativo Trabalhando com Views.

CTE SQL vs. View: Quando usar cada uma delas

Embora existam algumas diferenças entre elas, as expressões e visões comuns das mesas parecem ter um desempenho muito semelhante. Então, quando você deve usar cada uma delas?

• Consultas ad-hoc. Para consultas que são referenciadas ocasionalmente (ou apenas uma vez), geralmente é melhor usar um CTE. Se você precisar da consulta novamente, você pode simplesmente copiar o CTE e modificá-lo se necessário.
• Consultas freqüentemente usadas. Se você tende a referenciar a mesma consulta com freqüência, criar uma visão correspondente é uma boa idéia. Entretanto, você precisará criar uma permissão de visualização em seu banco de dados para criar uma visualização.
• Gerenciamento de acesso. Uma visualização pode ser usada para restringir o acesso de determinados usuários ao banco de dados, ao mesmo tempo em que lhes permite obter as informações necessárias. Você pode dar aos usuários acesso a vistas específicas que consultam os dados que eles têm permissão para ver sem expor todo o banco de dados. Em tal caso, uma visualização fornece uma camada de acesso adicional.

Vamos praticar os CTEs e Views SQL!

Agora que você tem uma compreensão básica dos CTEs e Views SQL, você está pronto para começar a usá-los em suas consultas! Praticar consultas SQL é a melhor maneira de entender como os CTEs funcionam e como as visões economizam tempo na digitação e execução de consultas.

LearnSQL.com.br desenvolveu vários cursos que cobrem estes tópicos em profundidade. Antes de mais nada, confira o Consultas Recursivas que o guiará desde os CTEs simples até os mais desafiadores CTEs recursivos. 114 exercícios interativos o ajudarão a dominar as expressões comuns da tabela da maneira mais eficiente.

Para aqueles interessados em aprender mais sobre as visualizações SQL, preparamos o curso Trabalhando com Visualizações. Este é um curso avançado que ensinará como criar, modificar e remover visualizações no SQL Server, MySQL, Oracle, e PostgreSQL.

Obrigado por ler, e feliz aprendizado!