In software engineering, it is common practice to group instructions as small and easily comprehensive units—namely functions or methods. Isto torna o código reutilizável e melhora a legibilidade.
embora SQL também tenha funções e procedimentos, eles não são as ferramentas certas para construir unidades facilmente compreensíveis e reutilizáveis. Em SQL, nem funções nem procedimentos são cidadãos de primeira classe da mesma forma que subquotas são.0 o bloco de construção do SQL são queries1—Não instruções.,
para tornar as consultas reutilizáveis, o SQL-92 introduziu pontos de vista. Uma vez criado, uma vista tem um nome no esquema de banco de dados para que outras consultas podem usá-lo como uma tabela.
SQL:1999 added the with clause to definite “statement scoped views”. Eles não são armazenados no esquema de banco de dados: em vez disso, eles são apenas válidos na consulta a que pertencem. Isso torna possível melhorar a estrutura de uma declaração sem poluir o espaço de nomes global.,
Sintaxe
with cláusula é, simplesmente, um prefixo opcional para select:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...A sintaxe após a palavra-chave with é o mesmo para o de create view: inicia-se com o nome da consulta, e optionally3 e, em parênteses, o nome das colunas que ele retorna. A palavra-chave as finalmente introduz a própria definição (a consulta)—novamente entre parênteses.,
With não é um autônomo de comandos como create view é: ele deve ser seguido por select. Esta consulta (e subqueries que contém) pode referir-se ao nome da consulta apenas definido em seu from cláusula.
com Um único with cláusula pode introduzir vários nomes de consulta separando-os com uma vírgula (with palavra-chave não é repetida)., Cada uma destas consultas, pode referir-se à consulta de nomes previamente definido dentro do mesmo with clause4 (que muitas vezes é negligenciado regra—consulte Compatibilidade):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...Consulta nomes definidos usando with máscara de tabelas existentes ou pontos de vista com o mesmo nome.5
considerações de desempenho
a maioria dos processos de bases de dados with-consultas da mesma forma que processam visões: elas substituem a referência à consulta por sua definição e otimizam a consulta geral.,
a base de dados PostgreSQL era diferente até a versão 12: ele otimizou cada id
consulta e a principal declaração independente uma da outra.
If a with query is referred to multiple times, some databases cache (i.e. “materialize”) its result to prevent double execution.
Read more about this in “with Clause: Performance Impacts”.ganho a vida a partir da formação, de outros serviços relacionados com a SQL e da venda do meu livro. Saiba mais em https://winand.at/.,
Casos de Uso
-
Alfabetizados SQL
-
testes de Unidade em transiente de dados
-
Atribuir nomes às colunas sem um nome conhecido
Compatibilidade
O with cláusula foi introduzido com o SQL:1999, como um conjunto de recursos opcionais. Deixando o recursive variante de lado, os recursos são T121 para nível superior with cláusulas e T122 para with cláusulas em subconsultas.
a funcionalidade básica de with é bem suportada., A única área em que os produtos se comportam de forma diferente é a resolução de nomes. It is especially noteworthy that with is often treated like with recursive.6 O mais sutil incompatibilidades estão relacionadas com nomes de tabela qualificado (schema.table não pode se referir a uma with consulta)7 e vistas utilizadas no âmbito da with consulta (consulta dentro do modo de exibição não “ver” o exterior with cláusula).,8
alternativas conformes
As opiniões podem abranger alguns dos casos de Utilização. No entanto, isso pode facilmente levar a um número irrazoável de pontos de vista (“poluição de espaços de nomes”). Nesses casos, as subcontas são muitas vezes a melhor opção.
extensões proprietárias
com prefixo DML (PostgreSQL, SQL Server, SQLite)
algumas bases de dados aceitam with como prefixo para declarações DML (docs: PostgreSQL, SQL Server, SQLite).,
SQL Server can also use a with query as a target for DML statements (basically building an updatable view).
Functions in with (Oracle)
The Oracle Database supports function and procedure declarations within the with clause since version 12cR1 (documentation).
DML com (PostgreSQL)
Começando com 9.1, o banco de dados PostgreSQL suporta instruções DML (insert update delete) dentro do corpo de with consultas (documentação)., Ao usar a cláusula (também proprietária) returning da declaração DML, a opção with consulta devolve de facto os dados (por exemplo, as linhas que acabaram de ser inseridas).