NULLs em agregações, junções e DISTINCT

NULL não se comporta da mesma forma em todos os contextos. A lição final aborda os três contextos cujo comportamento mais surpreende os candidatos: agregações, JOINs e DISTINCT / GROUP BY.

A diferença recorrente é que as agregações e a filtragem tratam NULL como “ignore-me”, enquanto o agrupamento e DISTINCT tratam NULL como “um valor igual a outros NULL”. Essa inconsistência é exatamente o que os entrevistadores exploram.

Domine esses conceitos e você terá completado o ciclo das perguntas mais comuns sobre NULL em entrevistas de SQL.

A regra principal é: as funções de agregação ignoram NULL. SUM, AVG, MIN, MAX e COUNT(column) ignoram completamente as entradas NULL, em vez de tratá-las como zero.

É por isso que AVG pode retornar um número diferente do esperado. Ele divide a soma dos valores não NULL pela quantidade de valores não NULL, e não pela quantidade total de linhas.

Esta é a pergunta mais frequente sobre NULL em agregações. COUNT(*) conta as linhas, inclusive aquelas que contêm NULL. COUNT(column) conta apenas as linhas em que essa coluna é não NULL.

Portanto, a diferença entre as duas funções corresponde exatamente à quantidade de valores NULL nessa coluna. COUNT(DISTINCT column) vai além: também ignora NULL ao remover duplicatas.

Os entrevistadores perguntam: “AVG(x) é igual a SUM(x) / COUNT(*)?”. A resposta é não quando há valores NULL.

AVG(x) é igual a SUM(x) / COUNT(x), dividindo pela quantidade de valores não NULL. Dividir por COUNT(*) em vez disso trata os valores NULL como se fossem zero, reduzindo a média.

Se você realmente quiser contar NULL como zero, deverá declarar isso explicitamente com COALESCE.

O que uma agregação retorna quando toda entrada é NULL ou quando não há linhas? Esta é uma distinção precisa que os entrevistadores apreciam:

• SUM, AVG, MIN e MAX sobre linhas totalmente NULL (ou sobre zero linhas) retornam NULL.
• COUNT sempre retorna 0, nunca NULL.

Assim, se um relatório exibir totais em branco, um SUM totalmente NULL é uma causa provável. Envolva-o em COALESCE para exibir 0.

Na cláusula ON de um JOIN, NULL = NULL continua sendo UNKNOWN, portanto chaves NULL nunca correspondem em um JOIN por igualdade. Duas linhas que tenham uma chave de JOIN NULL não serão associadas.

Isso costuma surpreender quem faz JOIN usando chaves estrangeiras opcionais. Se o comportamento desejado for corresponder NULL a NULL, você precisará de um operador seguro para NULL (IS NOT DISTINCT FROM ou <=>) apresentado na lição anterior.

Um JOIN externo gera valores NULL para linhas sem correspondência. Após um LEFT JOIN, toda coluna do lado direito será NULL nas linhas do lado esquerdo que não encontraram correspondência.

Essa é a base do padrão de anti-JOIN: filtre com WHERE right_table.key IS NULL para encontrar linhas sem correspondência, como clientes sem pedidos.

Tenha cuidado, porém: filtrar uma coluna do JOIN externo em WHERE pode convertê-lo acidentalmente novamente em um JOIN interno, tema da próxima cena.

Uma armadilha muito comum. Você faz um LEFT JOIN com pedidos e depois adiciona WHERE o.status = 'shipped'. De repente, os clientes sem pedidos desaparecem, transformando seu JOIN externo em um JOIN interno na prática.

Por quê? Nas linhas sem correspondência, o.status é NULL, e NULL = 'enviado' é UNKNOWN; por isso, WHERE as elimina. Para preservar as linhas sem correspondência, mova a condição para a cláusula ON.

Aqui está a inconsistência que surpreende todo mundo. As agregações ignoram NULL, mas DISTINCT mantém exatamente um NULL, tratando todos os NULL como duplicatas uns dos outros.

Assim, SELECT DISTINCT bonus sobre os valores 100, 100, NULL, NULL retorna três linhas: 100, NULL e nada mais. Os dois NULL são reduzidos a um só, embora NULL = NULL seja UNKNOWN em outros contextos.

GROUP BY segue a mesma regra de DISTINCT: todas as chaves NULL são reunidas em um único grupo. Isso é o oposto da lógica de comparação, na qual os NULL nunca são iguais entre si.

Assim, agrupar por uma coluna que aceita NULL fornece uma linha representando todos os registros cuja chave é NULL, que geralmente é exatamente o que se deseja em relatórios. Mencione esse contraste (agrupamento versus comparação) para demonstrar domínio do assunto.

A síntese central que impressiona os entrevistadores:

• As agregações ignoram NULL; AVG divide por COUNT(coluna), não por COUNT(*).
• COUNT(*) conta linhas; COUNT(coluna) e COUNT(DISTINCT coluna) ignoram NULL.
• SUM/AVG/MIN/MAX sobre nenhuma linha retornam NULL; COUNT retorna 0.
• Em junções, chaves NULL nunca correspondem; filtrar uma coluna de uma junção externa em WHERE a transforma silenciosamente em uma junção interna.
• DISTINCT e GROUP BY tratam todos os NULL como iguais, o oposto da lógica de comparação.

A frase resumida: 'NULL é ignorado ao agregar e comparar, mas é agrupado ao remover duplicatas.'

Teste o contraste entre agrupamento e agregação.

Você concluiu o tratamento de NULL para entrevistas:

• As agregações ignoram NULL; AVG divide pela quantidade de valores não NULL, e uma soma com todos os valores NULL resulta em NULL, enquanto COUNT resulta em 0.
• COUNT(*) inclui linhas NULL; COUNT(col) não inclui, e a diferença corresponde à quantidade de NULL.
• Chaves de junção que são NULL nunca correspondem; filtrar colunas de uma junção externa em WHERE pode reduzi-la a uma junção interna.
• DISTINCT e GROUP BY reúnem todos os NULL em um só, o inverso da lógica de comparação.

Lembre-se da máxima: NULL é ignorado ao agregar e comparar, mas é agrupado ao remover duplicatas. Essa única ideia responde à maioria das perguntas de entrevista sobre NULL.