Por que as consultas são analisadas de forma que não permite o uso de aliases de coluna na maioria das cláusulas?

Resumo

Não há nenhuma razão lógica para que isso não possa ser feito, mas o benefício é pequeno e existem algumas armadilhas que podem não ser imediatamente aparentes.

Resultados da pesquisa

Fiz algumas pesquisas e encontrei algumas boas informações. O seguinte é uma citação direta de uma fonte primária confiável (que deseja permanecer anônima) em 2012-08-09 17:49 GMT:

Quando o SQL foi inventado, ele não tinha aliases na cláusula SELECT. Esta foi uma falha séria que foi corrigida quando a linguagem foi padronizada pela ANSI por volta de 1986.

A linguagem foi planejada para ser "não processual" - em outras palavras, para descrever os dados que você deseja sem especificar como localizá-los. Portanto, até onde eu sei, não há razão para que uma implementação SQL não possa analisar toda a consulta antes de processá-la e permitir que aliases sejam definidos em qualquer lugar e usados ​​em qualquer lugar. Por exemplo, não vejo nenhum motivo para que a seguinte consulta não seja válida:

select name, salary + bonus as pay
from employee
where pay > 100000

Embora eu ache que esta é uma consulta razoável, alguns sistemas baseados em SQL podem introduzir restrições no uso de aliases por algum motivo relacionado à implementação. Não estou surpreso em saber que o SQL Server faz isso.

Estou interessado em mais pesquisas sobre o padrão SQL-86 e por que os DBMSes modernos não oferecem suporte à reutilização de alias, mas ainda não tive tempo de ir muito longe com isso. Para começar, não sei onde conseguir a documentação ou como descobrir quem exatamente compôs o comitê. Alguém pode ajudar? Também gostaria de saber mais sobre o produto Sybase original do qual veio o SQL Server.

A partir dessa pesquisa e de algumas reflexões adicionais, passei a suspeitar que o uso de aliases em outras cláusulas, embora bastante possível, simplesmente nunca foi uma prioridade tão alta para os fabricantes de DBMS em comparação com outros recursos de linguagem. Como não é um grande obstáculo, sendo facilmente contornado pelo criador de consultas, colocar esforço nisso em relação a outros avanços não é o ideal. Além disso, seria proprietário, pois obviamente não faz parte do padrão SQL (embora eu esteja esperando para descobrir mais sobre isso com certeza) e, portanto, seria uma pequena melhoria, quebrando a compatibilidade SQL entre DBMSes. Em comparação, CROSS APPLY(que na verdade nada mais é do que uma tabela derivada que permite referências externas) é uma grande mudança que, embora proprietária, ofereça um poder expressivo incrível que não é facilmente executado de outras maneiras.

Problemas com o uso de aliases em todos os lugares

Se você permitir que itens SELECT sejam colocados na cláusula WHERE, você pode não apenas explodir a complexidade da consulta (e, portanto, a complexidade de encontrar um bom plano de execução), mas também pode criar coisas completamente ilógicas. Tentar:

SELECT X + 5 Y FROM MyTable WHERE Y = X

E se MyTable já tiver uma coluna Y, a qual se refere a cláusula WHERE? A solução é usar um CTE ou uma tabela derivada, que na maioria dos casos não deve ter custo extra, mas obtém o mesmo resultado final. CTEs e tabelas derivadas, pelo menos, impõem a resolução de ambiguidade, permitindo que um alias seja usado apenas uma vez.

Além disso, não usar aliases na cláusula FROM faz muito sentido. Você não pode fazer isso:

SELECT
   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
FROM
   Table1 T
   INNER JOIN Table2 T2
      ON T2.ID = CalcID
   INNER JOIN Table3 T3
      ON T2.ID = T3.ID

Essa é uma referência circular (no sentido de que T2 está se referindo secretamente a um valor de T3, antes que a tabela tenha sido apresentada na lista JOIN) e muito difícil de ver. Que tal este:

INSERT dbo.FinalTransaction
SELECT
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   T.*
FROM
   dbo.MyTable T

Quanto você quer apostar que a função newid() será colocada duas vezes no plano de execução, fazendo com que as duas colunas mostrem valores diferentes de forma totalmente inesperada? E quando a consulta acima é usada N níveis profundos em CTEs ou tabelas derivadas. Garanto que o problema é pior do que você imagina. Já existem sérios problemas de inconsistência sobre quando as coisas são avaliadas apenas uma vez ou em que ponto de um plano de consulta, e a Microsoft disse que não corrigiráalguns deles porque estão expressando a álgebra de consulta corretamente - se alguém obtiver resultados inesperados, divida a consulta em partes. Permitir referências encadeadas, detectar referências circulares através de tais cadeias potencialmente muito longas – esses são problemas bastante complicados. Introduza o paralelismo e você terá um pesadelo em formação.

Nota: Usar o alias em WHERE ou GROUP BY não fará diferença nos problemas com funções como newid() ou rand().

Uma maneira do SQL Server para criar expressões reutilizáveis

CROSS APPLY/OUTER APPLY é uma maneira no SQL Server de criar expressões que podem ser usadas em qualquer outro lugar na consulta (mas não antes na cláusula FROM):

SELECT
   X.CalcID
FROM
   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   CROSS APPLY (
      SELECT
         T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   ) X
   INNER JOIN Table2 T2
      ON T2.ID = X.CalcID

Isso faz duas coisas:

1. Faz com que todas as expressões no CROSS APPLY obtenham um "namespace" (um alias de tabela, aqui, X) e sejam únicas dentro desse namespace.
2. Torna óbvio em todos os lugares não apenas que o CalcID vem do X, mas também torna óbvio por que você não pode usar nada do X ao unir as tabelas T1 e T3, porque o X ainda não foi introduzido.

Na verdade, gosto muito de CROSS APPLY. Tornou-se meu amigo fiel e uso-o o tempo todo. Precisa de um UNPIVOT parcial (o que exigiria um PIVOT/UNPIVOT ou UNPIVOT/PIVOT usando sintaxe nativa)? Feito com CROSS APPLY. Precisa de um valor calculado que será reutilizado muitas vezes? Feito. Precisa impor rigidamente a ordem de execução para chamadas em um servidor vinculado? Feito com uma melhoria gritante na velocidade. Precisa apenas de um tipo de linha dividida em 2 linhas ou com condições extras? Feito.

Portanto, no mínimo, no DBMS SQL Server 2005 e superior, você não tem mais motivos para reclamar: CROSS APPLY é como você DRY da maneira que deseja.

Não posso dizer os motivos exatos, mas direi que existem soluções alternativas para repetir expressões, por exemplo, usando CTEs, subconsultas, tabelas derivadas etc. para evitar a repetição.

Se você mostrar uma consulta com uma expressão repetida, provavelmente podemos mostrar como reescrevê-la para que a expressão seja listada apenas uma vez. No entanto, isso apenas reduz a complexidade na escrita/leitura da consulta, é improvável que mude muito sobre a eficiência. O SQL Server geralmente é muito bom em reconhecer que as expressões são repetidas e não executará esse trabalho duas vezes. Existem exceções que vão no sentido inverso, mas você só deve se preocupar com a eficiência quando de fato observar isso acontecendo. Eu suspeito que a maioria das expressões repetidas que você escreve são realmente recolhidas em apenas uma operação no plano.

Dito isso, também repetirei parte da minha resposta a esta pergunta:

https://dba.stackexchange.com/questions/19762/why-is-the-select-clause-listed-first

Aqui está a explicação de Joe Celko de como uma consulta é processada de acordo com o padrão (roubei isso do meu próprio artigo aspfaq.com , que provavelmente roubou a citação de uma postagem de grupo de notícias de Celko):

Aqui está como um SELECT funciona em SQL... pelo menos em teoria. Produtos reais otimizarão as coisas quando puderem.

Comece na cláusula FROM e construa uma tabela de trabalho de todas as junções, uniões, interseções e quaisquer outros construtores de tabela que estejam lá. A opção AS permite que você dê um nome a esta tabela de trabalho que você deve usar para o restante da consulta que a contém.

Vá para a cláusula WHERE e remova as linhas que não passam nos critérios; ou seja, que não teste para TRUE (rejeite UNKNOWN e FALSE). A cláusula WHERE é aplicada ao trabalho na cláusula FROM.

Vá para a cláusula opcional GROUP BY, crie grupos e reduza cada grupo a uma única linha, substituindo a tabela de trabalho original pela nova tabela agrupada. As linhas de uma tabela agrupada devem ter características de grupo: (1) uma coluna de agrupamento (2) uma estatística sobre o grupo (ou seja, funções agregadas) (3) uma função ou (4) uma expressão composta desses três itens.

Vá para a cláusula HAVING opcional e aplique-a na tabela de trabalho agrupada; se não houver cláusula GROUP BY, trate a tabela inteira como um grupo.

Vá para a cláusula SELECT e construa as expressões na lista. Isso significa que as subconsultas escalares, chamadas de função e expressões no SELECT são feitas depois que todas as outras cláusulas são feitas. O operador AS também pode dar um nome a expressões na lista SELECT. Esses novos nomes passam a existir de uma só vez, mas depois que a cláusula WHERE foi executada; você não pode usá-los na lista SELECT ou na cláusula WHERE por esse motivo.

As expressões de consulta aninhadas seguem as regras de escopo usuais que você esperaria de uma linguagem estruturada em bloco como C, Pascal, Algol, etc. Nomeadamente, as consultas mais internas podem fazer referência a colunas e tabelas nas consultas em que estão contidas.

Isso significa que um SELECT não pode ter mais colunas que um GROUP BY; mas certamente pode ter menos colunas.

Agora, Celko foi um dos principais contribuintes para as versões anteriores dos padrões. Não sei se algum dia você obterá uma resposta definitiva para a WHY?pergunta, exceto para especulação. Meu palpite é que listar a operação real primeiro torna muito fácil para o analisador saber exatamente qual será o tipo de operação. Imagine uma junção de 20 tabelas que pode acabar sendo um SELECTou UPDATEou DELETE, e lembre-se de que o código para esses mecanismos foi originalmente escrito na época em que a análise de strings era bastante cara.

Observe que, se o padrão SQL for ditado FROMpara vir primeiro, os fornecedores podem ter decidido independentemente analisar a gramática em uma ordem diferente; A Hora.

O mesmo é verdade para coisas como CASE. Já vimos cenários aqui neste site , por exemplo, onde o mito anteriormente acreditado de que CASEsempre processa em ordem e causa curto-circuito é falso. E isso também se estende a outras crenças comuns, como SQL Server avaliando junções na ordem em que foram escritas, cláusulas de curto-circuito WHEREda esquerda para a direita ou processamento de CTEs uma vez ou em uma determinada ordem, mesmo que sejam referenciadas várias vezes. Os produtos são livres para otimizar como acharem adequado, mesmo que não reflita exatamente como você afirmou que a consulta deve funcionar declarativamente.

No Entity SQL , você PODE usar aliases de expressões em outros locais da consulta em algumas situações:

select k1, count(t.a), sum(t.a)
from T as t
group by t.b + t.c as k1

Observe que aqui você DEVE definir a expressão na GROUP BYcláusula para usá-la na SELECTcláusula.

Obviamente, é possível permitir algum desse tipo de alias como expressão reutilizável em consultas SQL.