A mesma consulta de relatório de vinte linhas foi copiada em cinco painéis e cada cópia mudou um pouco. Um conta os pedidos cancelados, enquanto outro não. Visualizações SQL resolvem isso definindo essa lógica uma vez, no banco de dados, com um nome claro.

Executamos todos os exemplos abaixo no SQLite 3.50 e os resultados mostrados vêm dessa execução. As visualizações são SQL padrão, mas detalhes como atualização por meio de uma visualização, visualizações materializadas e tratamento de dependências variam entre os bancos de dados, e nós apontamos isso.

O que é uma visão?

Uma visualização é uma instrução SELECT salva no banco de dados com um nome. Quando você consulta a visualização, o banco de dados executa a consulta salva. Ela atua como uma tabela para leitura, mas uma visualização normal não contém sua própria cópia dos dados.

Pense em um filtro salvo em uma planilha. O filtro não é uma segunda cópia da planilha. É uma forma de olhar para os mesmos dados, que refletem sempre os valores mais recentes.

As tabelas de amostra

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Crie uma visualização

Esta visualização resume os pedidos de cada cliente. Ele esconde uma junção, agrupamento e uma expressão CASE atrás de um nome simples:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Agora qualquer um pode usá-lo, sem saber como funciona:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben aparece com zero, porque a visualização usa LEFT JOIN e COALESCE. Decisões como essa, tomadas uma vez na visualização, são exatamente o que mantém a consistência dos relatórios. Para atualizar sua compreensão do tratamento de NULL, consulte Valores SQL NULL.

As visualizações são sempre atuais

Como uma visualização normal sempre executa sua consulta, novos dados aparecem imediatamente. Adicionamos um pedido pago para Ben e consultamos a visualização novamente:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

Não houve etapa de atualização nem cópia para manter sincronizada. Esta é a principal diferença entre salvar os resultados da consulta em uma tabela separada.

Ocultando colunas

As visualizações costumam ser usadas para expor apenas as colunas que uma pessoa ou aplicativo precisa. Aqui, os endereços de e-mail e os limites de crédito são deixados de fora:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

Em bancos de dados com permissões, como PostgreSQL, SQL Server e MySQL, você pode conceder ao usuário de relatório acesso à visualização, mas não à tabela subjacente. SQLite não possui contas de usuário, então só poderíamos mostrar a própria restrição da coluna. Trate uma visualização como uma camada de proteção. Os dados confidenciais ainda precisam de controle de acesso adequado, e alguns bancos de dados oferecem segurança em nível de linha para filtrar linhas por usuário.

Desempenho: as visualizações não aumentam a velocidade

Um mito comum é que as visualizações tornam as consultas mais rápidas. Uma visualização normal é substituída por sua definição quando você a consulta, de modo que o banco de dados faz o mesmo trabalho como se você mesmo tivesse escrito a consulta completa. Nosso plano de execução mostrou a junção e o agrupamento da visualização sendo executados como parte da consulta:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

Portanto, uma visualização de uma consulta lenta ainda é uma consulta lenta. A velocidade vem dos índices e de um bom design de consulta, conforme abordado em Otimização de consulta SQL e Explicação sobre índices SQL. Cuidado também com as visualizações baseadas em outras visualizações. Camadas de aninhamento facilitam a união da mesma mesa grande várias vezes sem perceber.

Visualizações materializadas

Uma visão materializada armazena seus resultados, como uma tabela em cache. As leituras são rápidas, mas os dados são tão recentes quanto a última atualização.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

O suporte varia. PostgreSQL e Oracle possuem visualizações materializadas e o SQL Server oferece visualizações indexadas com suas próprias regras. SQLite e MySQL não os possuem, então as pessoas usam uma tabela de resumo regular atualizada de acordo com uma programação. Não executamos o exemplo do PostgreSQL para este artigo.

Você pode atualizar uma visualização?

Depende do banco de dados e da visualização. Uma visualização simples de uma única tabela, sem agrupamento ou DISTINCT, muitas vezes pode ser atualizada no PostgreSQL, MySQL e SQL Server, e a alteração passa para a tabela. Uma visualização com junções ou agregações geralmente não pode, porque o banco de dados não consegue dizer qual linha alterar.

O SQLite não permite gravações diretas nas visualizações. Nossa tentativa falhou:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Alguns bancos de dados suportam gatilhos INSTEAD OF para tornar uma visualização gravável, e muitos oferecem WITH CHECK OPTION, que interrompe gravações que não seriam visíveis na visualização. Na prática, a maioria das equipes mantém as visualizações somente leitura e grava nas tabelas.

Dependências e quebras

Uma visualização depende das tabelas das quais ela lê. Criamos uma visualização em uma tabela e depois eliminamos a tabela:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite permitiu a queda e só falhou mais tarde, quando a view foi usada. Outros bancos de dados, como PostgreSQL, recusam-se a eliminar uma tabela da qual uma visualização depende, a menos que você adicione CASCADE, o que também remove a visualização. De qualquer forma, renomear ou eliminar uma coluna pode interromper as visualizações, portanto, verifique-as em seu teste de regressão após alterações no esquema.

Há também uma armadilha sutil com SELECT * em uma visualização. Criamos CREATE VIEW star AS SELECT * FROM customers e depois adicionamos uma coluna à tabela. No SQLite, a visualização pegou a nova coluna. Outros bancos de dados, incluindo o PostgreSQL, expandem a lista de colunas quando a visualização é criada, para que novas colunas não apareçam. Portanto, liste as colunas explicitamente. Ele documenta a visualização e se comporta da mesma forma em todos os lugares.

Gerenciando visualizações

A eliminação de uma visualização remove apenas a consulta salva. Em nosso teste, a tabela customers ainda tinha todas as três linhas após eliminar public_customers. Mantenha as definições de visualização no controle de versão junto com suas migrações, para que as alterações sejam revisadas como qualquer outro código.

Ver vs CTE vs tabela

Erros comuns

• Esperando um aumento de velocidade. Uma visualização normal executa sua consulta todas as vezes.
• Usando SELECT * em visualizações. O comportamento difere entre bancos de dados quando as tabelas são alteradas.
• Aninhamento profundo. Visualizações criadas em visualizações ocultam junções caras.
• Usando ORDER BY dentro de uma visualização. A ordem não é garantida quando você a consulta, então classifique na consulta externa.
• Nomes pouco claros. Um prefixo como v_ ou um substantivo claro como customer_sales ajuda.
• Esquecimento de visualizações durante migrações. Teste-as após cada alteração de esquema.

Considerações finais

As visualizações são uma maneira simples de fornecer uma lógica importante no banco de dados. Use-os para manter os relatórios consistentes e ocultar colunas desnecessárias, não como um truque de desempenho. Para começar, encontre uma consulta que apareça em vários lugares do seu código, transforme-a em uma visualização com colunas explícitas e aponte cada chamador para ela.

Perguntas frequentes

Continue aprendendo