Um Guia para Principiantes para a Verdadeira Ordem das Operações SQL

A linguagem SQL é muito intuitiva. Até que não é.

A linguagem SQL tem sido criticada por muitas pessoas por diversas razões. Por exemplo, a linguagem SQL: IDEs não podem facilmente adivinhar que opções de auto-completar para oferecer, porque enquanto você não especificar a cláusula FROM, não há tabelas no escopo (ainda):

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Estas coisas são estranhas, porque a ordem lexical das operações não corresponde à ordem lógica das operações. Nós humanos podemos às vezes (muitas vezes) intuitivamente entender essa diferença de ordenação. Por exemplo, sabemos que estamos prestes a seleccionar a partir da tabela de clientes. Mas a IDE não sabe isto.

GROUP BY contribui com mais confusão

Quando um desenvolvedor júnior / iniciante em SQL começa a trabalhar com SQL, muito rapidamente, eles vão descobrir sobre agregação e GROUP BY. E eles escreverão rapidamente coisas como:

SELECT count(*)FROM customer

Yay, nós temos 200 clientes!

E então:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Wow, 90 deles são chamados de Steve! Interessante. Vamos descobrir quantos temos por nome…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Muito bom. Mas eles são todos iguais? Vamos ver o último nome, também

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jeez, o que significa? (note, infelizmente, os utilizadores do MySQL que não utilizam o modo STRICT ainda terão aqui um resultado com apelidos arbitrários!, para que um novo utilizador do MySQL não entenda o seu erro)

Como explica isto facilmente a um novato do SQL? Parece óbvio para “profissionais”, mas será mesmo óbvio? É óbvio o suficiente que você possa explicar isso facilmente para um novato? Pense sobre isso. Porque é que cada uma destas instruções está semanticamente correcta ou errada?

O problema é relacionado com a sintaxe

A sintaxe SQL funciona de uma forma semelhante à da língua inglesa. É um comando. Nós iniciamos os comandos com verbos. O verbo é SELECT (ou INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Felizmente, a linguagem humana é incrivelmente inadequada para o mundo muito mais formal da programação. Enquanto oferece algum consolo aos novos usuários (possivelmente não programadores) que são iniciantes absolutos, ela apenas torna as coisas difíceis para todos os outros. Todas as diferentes cláusulas SQL têm interdependências extremamente complexas. Por exemplo:

• Na presença de uma cláusula GROUP BY, somente expressões construídas a partir de GROUP BY expressões (ou dependências funcionais das mesmas), ou funções agregadas podem ser usadas em HAVING, SELECT, e ORDER BY cláusulas.
• Por razões de simplicidade, não vamos nem falar de GROUPING SETS
• Na verdade, há até alguns casos em que GROUP BY está implícito. Por exemplo se você escrever uma “naked” HAVING cláusula
• Uma única função agregada na cláusula SELECT (na ausência de GROUP BY) irá forçar a agregação em uma única linha
• Na verdade, isso também pode ser implícito colocando essa função agregada em ORDER BY (por qualquer razão)
• Você pode ORDER BY várias expressões que referenciam qualquer coluna da cláusula FROM sem SELECT as fazer. Mas isso não é mais verdade se você escrever SELECT DISTINCT
• …

A lista é interminável. Se você estiver interessado, você pode ler os documentos padrão SQL e verificar quantas inter-dependências estranhas e complicadas existem entre as muitas cláusulas da instrução SELECT

Pode isto alguma vez ser entendido?

Felizmente, sim! Há um truque simples, que eu estou sempre explicando aos delegados que visitam a minha SQL Masterclass. A ordem léxica (sintáctica) das operações SQL (cláusulas) não corresponde em nada à ordem lógica das operações (embora, por vezes, coincidam). Graças aos otimistas modernos, a ordem também não corresponde à ordem real das operações, portanto temos realmente: sintática -> lógica -> ordem real, mas vamos deixar isso de lado por enquanto.

A ordem lógica das operações é a seguinte (para “simplicidade” estou deixando de fora coisas específicas do fornecedor como CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT e todas as outras):

• FROM: Esta é na verdade a primeira coisa que acontece, logicamente. Antes de mais nada, estamos a carregar todas as filas de todas as tabelas e juntarmo-nos a elas. Antes de você gritar e ficar bravo: Mais uma vez, isto é o que acontece primeiro logicamente, não realmente. O otimizador muito provavelmente não vai fazer essa operação primeiro, isso seria bobagem, mas acessar algum índice baseado na cláusula WHERE. Mas mais uma vez, logicamente, isto acontece primeiro. Também: todas as cláusulas JOIN são na verdade parte desta cláusula FROM. JOIN é um operador em álgebra relacional. Assim como + e - são operadores em aritmética. Não é uma cláusula independente, como SELECT ou FROM
• WHERE: Uma vez carregadas todas as linhas das tabelas acima, podemos agora deitá-las fora novamente usando WHERE
• GROUP BY: Se você quiser, você pode pegar as linhas que restam após WHERE e colocá-las em grupos ou baldes, onde cada grupo contém o mesmo valor para a expressão GROUP BY (e todas as outras linhas são colocadas em uma lista para esse grupo). Em Java, você obteria algo parecido: Map<String, List<Row>>. Se você especificar uma cláusula GROUP BY, então suas linhas reais contêm apenas as colunas do grupo, não mais as colunas restantes, que agora estão nessa lista. Essas colunas na lista só são visíveis para agregar funções que podem operar nessa lista. Veja abaixo.
• agregações: Isto é importante para entender. Não importa onde você coloque sua função agregada sintaticamente (ou seja, na cláusula SELECT, ou na cláusula ORDER BY), esta é a etapa onde as funções agregadas são calculadas. Logo a seguir a GROUP BY. (lembre-se: logicamente. Bases de dados inteligentes podem tê-las calculado antes, na verdade). Isto explica porque você não pode colocar uma função agregada na cláusula WHERE, porque seu valor ainda não pode ser acessado. A cláusula WHERE acontece logicamente antes da etapa de agregação. As funções agregadas podem acessar colunas que você colocou em “esta lista” para cada grupo, acima. Após a agregação, “esta lista” irá desaparecer e não estará mais disponível. Se você não tiver uma cláusula GROUP BY, haverá apenas um grande grupo sem qualquer chave, contendo todas as linhas.
• HAVING: … mas agora você pode acessar os valores das funções de agregação. Por exemplo, você pode verificar que count(*) > 1 na cláusula HAVING. Porque HAVING está depois de GROUP BY (ou implica GROUP BY), não podemos mais acessar colunas ou expressões que não foram GROUP BY colunas.
• WINDOW: Se você está usando o recurso de função de janela impressionante, este é o passo onde todos eles são calculados. Somente agora. E o legal é que, como já calculamos (logicamente!) todas as funções agregadas, podemos aninhar funções agregadas em funções de janela. Portanto, é perfeitamente bom escrever coisas como sum(count(*)) OVER () ou row_number() OVER (ORDER BY count(*)). Funções de janela sendo calculadas logicamente apenas agora também explica porque você pode colocá-las apenas nas cláusulas SELECT ou ORDER BY. Elas não estão disponíveis para a cláusula WHERE, o que aconteceu antes. Note que PostgreSQL e Sybase SQL Anywhere tem uma cláusula WINDOW real!
• SELECT: Finalmente. Agora podemos usar todas as linhas que são produzidas a partir das cláusulas acima e criar novas linhas / tuplos a partir delas usando SELECT. Podemos acessar todas as funções da janela que calculamos, todas as funções agregadas que calculamos, todas as colunas de agrupamento que especificamos, ou se não agrupamos/agregamos, podemos usar todas as colunas da nossa cláusula FROM. Lembre-se: Mesmo que pareça que estamos agregando coisas dentro de SELECT, isto já aconteceu há muito tempo, e a função sweet sweet count(*) nada mais é do que uma referência ao resultado.
• DISTINCT: Sim! DISTINCT acontece depois de SELECT, mesmo que seja colocado antes da sua lista de colunas SELECT, em termos de sintaxe. Mas pense sobre isso. Faz todo o sentido. De que outra forma podemos remover linhas distintas, se ainda não conhecemos todas as linhas (e suas colunas)?
• UNION, INTERSECT, EXCEPT: Isto é um “nobrainer”. A UNION é um operador que liga duas subconsultas. Tudo o que falamos até agora foi uma subconsulta. A saída de uma união é uma nova consulta contendo os mesmos tipos de linhas (ou seja, as mesmas colunas) que a primeira subconsulta. Normalmente. Porque em wacko Oracle, a penúltima subconsulta é a correta para definir o nome da coluna. Oracle database, o troll sintáctico 😉
• ORDER BY: Faz total sentido adiar a decisão de ordenar um resultado até o final, porque todas as outras operações podem usar hashmaps, internamente, então qualquer ordem intermediária pode ser perdida novamente. Assim, podemos agora encomendar o resultado. Normalmente, você pode acessar muitas linhas a partir da cláusula ORDER BY, incluindo linhas (ou expressões) que você não fez SELECT. Mas quando você especificou DISTINCT, antes, você não pode mais ordenar por linhas/expressões que não foram selecionadas. Por quê? Porque a ordenação seria bastante indefinida.
• OFFSET: Não use offset
• LIMIT, FETCH, TOP: Agora, bancos de dados sãos colocam a cláusula LIMIT (MySQL, PostgreSQL) ou FETCH (DB2, Oracle 12c, SQL Server 2012) no final, sintaticamente. Antigamente, Sybase e SQL Server pensavam que seria uma boa idéia ter TOP como palavra-chave em SELECT. Como se a ordem correcta de SELECT DISTINCT já não fosse suficientemente confusa.

There, we have it. Faz todo o sentido. E se você quiser fazer algo que não esteja na “ordem certa”, o truque mais simples é sempre recorrer a uma tabela derivada. Por exemplo, quando você quer agrupar em uma função de janela:

Por que isso funciona? Porque:

• Na tabela derivada, FROM acontece primeiro, e depois o WINDOW é calculado, depois o balde é SELECTed.
• O exterior SELECT pode agora tratar o resultado do cálculo desta função de janela como qualquer tabela normal na cláusula FROM, depois GROUP BY uma coluna normal, depois agregada, depois SELECT

Vejamos os nossos exemplos originais com uma explicação do porquê de funcionarem ou não.

Pense sempre na ordem lógica das operações

Se você não é um escritor frequente de SQL, a sintaxe pode de fato ser confusa. Especialmente GROUP BY e as agregações “infectam” o resto da cláusula SELECT, e as coisas ficam realmente estranhas. Quando confrontados com esta esquisitice, temos duas opções:

• Enlouquecer e gritar com os designers da linguagem SQL
• Aceitar o nosso destino, fechar os olhos, esquecer o snytax e lembrar a ordem lógica das operações

Eu geralmente recomendo esta última, porque então as coisas começam a fazer muito mais sentido, incluindo o belo cálculo da receita diária acumulada abaixo, que aninha a receita diária (SUM(amount) função agregada) dentro da receita acumulada (SUM(...) OVER (...) função janela):

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… porque as agregações acontecem logicamente antes das funções de janela.

Caveat: ORDER BY clause

Existem algumas advertências em torno da cláusula ORDER BY, o que pode estar contribuindo para uma maior confusão. Por padrão, continue assumindo que a ordem lógica das operações esteja correta. Mas então, há alguns casos especiais, em particular:

• Na ausência de uma DISTINCT cláusula
• Na ausência de operações definidas como UNION

É possível referenciar expressões em ORDER BY, que não são projetadas por SELECT. A seguinte consulta está perfeitamente bem na maioria das bases de dados:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Existe uma projecção “virtual” / implícita ACTOR_ID, como se tivéssemos escrito:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Mas depois, remova novamente a coluna ACTOR_ID do resultado. Isto é muito conveniente, embora possa levar a alguma confusão sobre a semântica e a ordem das operações. Especificamente, você não pode usar, por exemplo, DISTINCT em tal situação. A seguinte consulta é inválida:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Porquê, e se houver dois actores com o mesmo nome mas com IDs muito diferentes? A ordenação seria agora indefinida.

Com operações definidas, é ainda mais claro porque é que isto não é permitido:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

Neste caso, a coluna ACTOR_ID não está presente na tabela CUSTOMER, por isso a consulta não faz sentido.

Outra leitura

Quer saber mais? Nós também temos estes artigos para você ler:

• SQL GROUP BY e Functional Dependencies: Uma Característica Muito Útil
• Como o SQL GROUP BY deveria ter sido projetado – Como o Neo4j’s Implicit GROUP BY
• Como Traduzir SQL GROUP BY e Agregações para Java 8
• Você Entende Realmente o SQL GROUP BY e as cláusulas HAVING?
• GROUP BY ROLLUP / CUBE