NULLs e tratamento de dados ausentes no SQL

O manuseio de dados ausentes (ou seja, NULLs) no SQL pode ser desafiador. Os NULLs podem representar muitas armadilhas, especialmente se você não entender como eles funcionam. Neste artigo, falaremos sobre como lidar com NULL no SQL. Também explicaremos como evitar erros comuns ao trabalhar com NULLs.

Ter dados faltando em seu banco de dados é uma realidade inevitável da vida. Há muitos motivos pelos quais pode haver dados ausentes no banco de dados: os dados completos ainda não estão disponíveis, os usuários fornecem informações incompletas, alterações no esquema do banco de dados, mau funcionamento do banco de dados, erro humano e outros. Trabalhar com dados ausentes em consultas SQL é um desafio. Neste artigo, mostrarei as armadilhas mais comuns associadas a dados incompletos em SQL.

Se você quiser praticar o trabalho com dados ausentes ou desconhecidos no SQL, recomendo nossa trilhaTrilha de Práticas em SQL . No momento em que escrevo, ela contém 10 cursos para você praticar SQL - e continuamos adicionando mais! Os cursos são divididos em seções e muitos deles têm uma seção dedicada a NULL. Recomendo especificamente esses cursos para praticar a manipulação de NULL em SQL:

• Básico Trilha de Práticas em SQL: A Store
• Trilha de Práticas em SQL: Universidade
• Exercícios SQL Básico: Treine como um atleta
• Básico Trilha de Práticas em SQL: Blog e dados de tráfego

O que é NULL em SQL?

Em SQL, NULL representa um valor ausente ou indefinido em um banco de dados. É usado para denotar o fato de que o valor em um campo está ausente ou é desconhecido.

Um problema comum para iniciantes com NULLs é que seu banco de dados muitas vezes não mostrará NULLs explicitamente. Na tabela abaixo, o campo like para o ID de postagem 1, o campo location para o ID de postagem 2 e o campo views para o ID de postagem 3 são todos NULL. Entretanto, um banco de dados não mostra NULL explicitamente; em vez disso, mostra um campo vazio. Você deve estar ciente de que NULL é uma possibilidade de adivinhar que esses campos são NULL.

id	title	location	views	likes
1	Quick Morning Routines!	London	94,365
2	Eco-Friendly Living Tips		123,891	6,587
3	Healthy Snacks on the Go	Paris		9,457

É importante observar, entretanto, que NULL é diferente de uma cadeia de caracteres vazia ou de um zero. NULL é a ausência de valor; significa que o valor é desconhecido. Como veremos em breve, o próprio NULL não é, na verdade, um valor real. Muitas pessoas dizem ou escrevem algo como "Há NULL valores neste campo", mas isso é tecnicamente incorreto.

Ao projetar uma tabela em um banco de dados, um designer de banco de dados pode decidir que NULL não é permitido para um determinado campo. Isso é feito definindo uma restrição NOT NULL para uma coluna quando a tabela é criada. As chaves primárias (colunas que identificam linhas em uma tabela) também são NOT NULL por padrão.

É considerada uma prática recomendada evitar NULLs em seu projeto de banco de dados. Portanto, o maior número possível de colunas deve ser definido como NOT NULL. É melhor permitir NULLs somente quando for estritamente necessário. Entretanto, os dados podem ser confusos e, às vezes, o NULL é inevitável.

O conjunto de dados

Neste artigo, usaremos os dados da tabela posts. Imagine que você esteja extraindo dados da sua plataforma de mídia social favorita para fazer uma análise. Os dados dessa plataforma estão armazenados nesta tabela. Aqui estão os campos:

• id - O ID da postagem.
• title - O título da publicação.
• url - O URL (endereço da Web) da publicação.
• creator - O nome do criador da postagem.
• published - A data em que a postagem foi publicada.
• type - O tipo da postagem.
• location Onde a postagem foi publicada; pode ser NULL se o local for desconhecido ou irrelevante.
• views - Quantas visualizações cada postagem tem; pode ser NULL se o criador optar por não tornar esses dados visíveis publicamente.
• likes - O número de curtidas dadas à publicação; pode ser NULL se o criador optar por não tornar esses dados visíveis publicamente.
• dislikes - O número de não curtidas dadas à publicação; pode ser NULL porque a plataforma não mostra mais esses dados. No entanto, podemos ter os dados de não curtidas de publicações mais antigas.

Agora que analisamos os dados, vamos usá-los para entender NULL.

Operadores de comparação com NULL

Mesmo algo aparentemente simples como o comportamento do NULL quando usado com operadores de comparação pode ser contraintuitivo e surpreendente para iniciantes.

Suponhamos que queiramos descobrir quantas linhas da tabela posts têm campos views ausentes. Por exemplo:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

Resultado: 0 linhas

Ótimo, zero linhas têm visualizações ausentes. Incrível! Quantas delas não estão faltando, então?

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Resultado: 0 linhas

Também zero? Algo deve estar errado.

O problema aqui é que você deve usar os operadores IS NULL and IS NOT NULL para testar NULLs:

SELECT COUNT(*)
FROM posts
WHERE views IS NULL;

Resultado: 34 linhas

SELECT COUNT(*)
FROM posts
WHERE views IS NOT NULL;

Resultado: 66 linhas

Por que esses resultados são tão diferentes das duas consultas anteriores?

Lógica de três valores em SQL

Os problemas de comparação para NULL vêm do fato de que NULL não é um valor real. Esse é o ponto mais importante que você precisa entender para trabalhar com NULL de forma eficiente.

O SQL usa a lógica de três valores. Cada condição lógica no SQL pode ter um de três valores: TRUE, FALSE ou NULL. NULL aqui significa "I don't know" (Não sei). Sempre que você usa uma condição em WHERE, o SQL retorna linhas para as quais a condição lógica em WHERE é TRUE. Ele não retorna as linhas para as quais a condição é FALSE (como você esperaria) e para as quais a condição é NULL (que nem sempre é o que você esperaria).

Vamos considerar um exemplo de como funciona o WHERE:

SELECT COUNT(*)
FROM posts
WHERE views < 100;

Essa consulta conta os posts em que a coluna views tem um valor e esse valor é menor que 100. As postagens com visualizações desconhecidas não são contadas. Isso pode ser contraintuitivo: quando você exibe os dados dos posts, vê o vazio em muitos campos de visualizações. Intuitivamente, você pensa que certamente esse vazio é menor que 100. Mas o vazio significa que o banco de dados não tem os dados; como ele não tem os dados, não pode dizer se é menor que 100 ou não. Portanto, as linhas que têm um campo views vazio não são contadas.

SELECT COUNT(*)
FROM posts
WHERE views < likes;

Essa consulta retorna linhas em que o número de visualizações e o número de curtidas são ambos conhecidos e o valor views é menor que o valor likes. Ela não retorna linhas em que o valor views é desconhecido ou o valor likes é desconhecido.

Se você quiser incluir as publicações com campos views vazios no resultado, deverá filtrar explicitamente por NULL:

SELECT COUNT(*)
FROM posts
WHERE views < likes OR views IS NULL;

Vamos voltar novamente às consultas com as quais começamos:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

A condição WHERE compara a coluna views com NULL. Entretanto, NULL significa "I don't know the value" (Não sei o valor). O banco de dados não pode dizer se views é igual (ou não) a um valor desconhecido. Pode ser ou não, então o banco de dados diz NULL - ou seja, "I don't know" (Não sei) - e essas linhas não são retornadas no resultado.

Lembre-se:

• Teste para NULL com IS NULL e IS NOT NULL
• Os operadores de comparação (como <, <=, >, >=, =, <> e LIKE) retornam NULL se um dos argumentos for NULL. Se você quiser incluir NULL, teste-o explicitamente com IS NULL ou IS NOT NULL.

Uso de NULL em funções SQL

NULL é igualmente problemático em operadores e funções. A maioria das funções e operadores retorna NULL quando recebe NULL como argumento.

Exemplo 1: Imagine que queremos retornar o cabeçalho de cada postagem. (O cabeçalho consiste no título, um traço e o local). Aqui está a consulta:

SELECT 
  title || ‘ - ‘ || location
FROM posts;

Resultado:

Quick Morning Routines! - London
Healthy Snacks on the Go - Paris

A consulta retorna NULL se o title ou o location estiverem faltando. A postagem com ID 2 tem NULL como resultado de nossa consulta, pois seu location é desconhecido.

Exemplo 2: O mesmo se aplica aos operadores aritméticos. Digamos que você queira calcular o engajamento do post como a soma de likes e dislikes:

SELECT 
  title, 
  likes, 
  dislikes, 
  likes + dislikes AS engagement
FROM posts;

Resultado:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10
Healthy Snacks on the Go		34

Se qualquer um dos campos likes ou dislikes for NULL, então o valor retornado na coluna de engajamento também será NULL.

Exemplo 3: O mesmo comportamento é exibido por funções regulares, como UPPER():

SELECT 
  title, 
  UPPER(creator)
FROM posts;

title	UPPER(creator)
Quick Morning Routines!	JENNY
Eco-Friendly Living Tips
Healthy Snacks on the Go	RACHEL82

O criador da postagem "Eco-Friendly Living Tips" é desconhecido e, portanto, a expressão UPPER(creator) retorna NULL.

Funções que funcionam com NULLs

Felizmente, há funções no SQL que ajudam a mitigar esses problemas com NULL.

COALESCE

COALESCE() recebe muitos argumentos e retorna o primeiro valor nãoNULL de seus argumentos. Normalmente, ela é usada para substituir NULL por um valor significativo em outra função ou expressão. Poderíamos modificar nossa consulta de compromisso da seguinte forma:

SELECT 
  title, 
  likes, 
  dislikes, 
  COALESCE(likes, 0) + COALESCE(dislikes, 0) AS engagement
FROM posts;

Sempre que o valor de likes ou dislikes for NULL, a função COALESCE() o substitui por 0. O novo valor é usado no cálculo e evitamos os resultados de NULL:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10		10
Healthy Snacks on the Go		34	34

Você também pode usar COALESCE() para dar um rótulo significativo para NULLs nos resultados. A consulta a seguir substitui NULL por "Unknown" no conjunto de resultados; o campo em si ainda é NULL no banco de dados:

SELECT 
  title, 
  COALESCE(location, ‘Unknown’) AS location
FROM posts;

Aqui está o resultado:

title	location
Quick Morning Routines!	London
Eco-Friendly Living Tips	Unknown
Healthy Snacks on the Go	Paris

Lembre-se: você usa a função COALESCE():

• Para fornecer um rótulo significativo para NULL nos relatórios.
• Para fornecer um valor para NULL em cálculos.

NULLIF

Outra função que trabalha com NULL é NULLIF. Essa função é um pouco estranha: ela recebe dois argumentos e retorna NULL se os argumentos forem iguais. Na prática, você usa NULLIF para evitar a divisão por zero:

SELECT 
  title, 
  likes / NULLIF(views, 0)
FROM posts;

Você deseja calcular a proporção entre likes e views para posts. Entretanto, se o valor de views for 0, você poderá receber um erro de divisão por zero. Para evitar isso, você usa a função NULLIF. Se views for igual a zero, então NULLIF(views, 0) retornará NULL.

NULL A divisão resulta em um resultado NULL e evita o erro de divisão por zero. Aqui aproveitamos o NULL em cascata sobre os resultados dos cálculos.

NULL em funções GROUP BY e Aggregate

Ao trabalhar com valores ausentes, é bom saber como NULL se comporta em GROUP BY e funções agregadas.

NULL e GROUP BY

GROUP BY colocam as linhas em grupos com base em valores comuns em uma determinada coluna. Em seguida, você pode aplicar funções de agregação a cada grupo e calcular resumos para cada grupo. Essa consulta conta o número de postagens para cada local:

SELECT 
  location, 
  COUNT(*)
FROM posts
GROUP BY location;

Com GROUP BY, todas as linhas com NULL na coluna são colocadas em um grupo; você calcula estatísticas para esse grupo como qualquer outro.

Em nosso exemplo, todas as postagens com um local desconhecido são colocadas em um grupo:

location	COUNT
London	45
Paris	23
	12
…	…

NULL e funções agregadas

De modo geral, as funções de agregação também ignoram NULLs. Mas há algumas variações importantes na forma como algumas funções agregadas lidam com NULLs.

As funções SUM(), MIN(), MAX() ignoram NULLs:

SELECT 
  type, 
  SUM(views), 
  MIN(views), 
MAX(views)
FROM posts
GROUP BY type;

type	SUM	MIN	MAX
video	230,485	5,632	100,589
image	159,340	1,289	45,003
text	34,224	256	3,341
infographics

A função SUM() trata NULL como se fosse 0, portanto, NULL não influencia o resultado de SUM. Mas se todos os valores do grupo forem NULL, o resultado de SUM() será NULL. Em nosso exemplo, não temos dados de visualização para o grupo de infográficos, portanto, a soma é NULL para esse grupo.

As funções MIN() e MAX() também ignoram NULL; elas retornam os valores mínimo e máximo dos valores conhecidos. Somente se todos os valores do grupo forem NULL é que essas funções retornarão NULL. Nosso grupo de infográficos não tem dados, portanto, os valores mínimo e máximo são informados como NULL.

A função COUNT() é um pouco mais sutil quando se trata de lidar com NULL. Há três variantes da sintaxe de COUNT: COUNT(*), COUNT(expression), COUNT(DISTINCT). Você pode ler sobre elas em nosso artigo Qual é a diferença entre COUNT(*), COUNT(1), COUNT(column) e COUNT(DISTINCT)?

SELECT 
  COUNT(*), 
  COUNT(location), 
  COUNT(DISTINCT location)
FROM posts;

COUNT	COUNT	COUNT
100	78	52

A expressão COUNT(*) conta todas as linhas do conjunto de resultados. Há 100 posts em nossa tabela posts tabela, portanto, essa expressão retorna 100.

A expressão COUNT(location) conta os valores nãoNULL na coluna fornecida. Em nosso exemplo, ela contará as publicações em que a coluna location não for NULL. Ela ignorará as publicações com locais desconhecidos.

Por fim, a expressão COUNT(DISTINCT location) conta valores distintos que não sejamNULL; em outras palavras, ela ignora valores repetidos. Ele contará quantos locais diferentes existem em nossa tabela posts tabela.

A função AVG() ignora NULL. Em geral, isso é o que se espera. Entretanto, você deve ter cuidado ao usar AVG() com COALESCE(). Todas as variantes a seguir retornam valores diferentes: AVG(views), AVG(COALESCE(views,0)), COALESCE(AVG(views)).

Lembre-se:

• As linhas com NULLs nas colunas GROUP BY são colocadas em um grupo separado.
• As funções de agregação ignoram NULL e usam apenas valores conhecidos nos cálculos.
• Use COALESCE se quiser substituir um valor desconhecido por um valor específico.

NULL e JOIN

Você deve se lembrar de NULL ao usar JOIN, especialmente com OUTER JOINs como LEFT JOIN ou FULL JOIN. Pode haver NULLs em colunas provenientes da tabela correta.

Imagine que temos outra tabela, commentsque contém dados sobre comentários de postagens. Ela tem informações nas seguintes colunas:

• id - Um identificador exclusivo para cada comentário.
• post_id - O ID da publicação sobre a qual o comentário se refere.
• content - O conteúdo do comentário
• author - O autor do comentário
• upvotes - O número de votos positivos dados a esse comentário, que pode ser NULL
• downvotes - O número de downvotes dados a esse comentário; pode ser NULL

Queremos contar quantos comentários existem para cada publicação, mas queremos incluir publicações sem comentários nos resultados. Você precisa usar posts LEFT JOIN comments para incluir todos os posts.

Em seguida, você deve se lembrar de usar COUNT(comments.id) e não COUNT(*) ao contar os comentários. Esse último contará as linhas independentemente de a linha estar relacionada ao comentário. A maneira correta é usar COUNT(comments.id). Se não houver comentários, o id será NULL e não será contado.

SELECT 
  posts.title, 
  COUNT(comments.id)
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id;

Outro problema que deve ser lembrado é que a condição WHERE pode, às vezes, "cancelar" a condição OUTER JOIN. Na consulta abaixo, queremos encontrar comentários com upvotes maior que 100. Se a publicação tiver alguns comentários com um número desconhecido de votos positivos, esses comentários não serão incluídos no resultado. Se a publicação tiver apenas comentários com um número desconhecido de votos positivos, a publicação não será incluída de forma alguma, apesar de usarmos LEFT JOIN. A condição WHERE efetivamente "cancelará" a condição LEFT JOIN:

SELECT 
  posts.title, 
  comments.content
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id
WHERE upvotes > 100;

Lembre-se:

• LEFT JOIN RIGHT JOIN, ou podem introduzir no resultado. FULL JOIN NULL
• A condição WHERE pode "cancelar" a condição OUTER JOIN.

NULL em ORDER BY

Ao criar um relatório, você geralmente deseja classificar os dados em uma ordem específica, por exemplo, em ordem alfabética, ascendente ou descendente. Como o site NULL se comporta na classificação?

Quando você ordena por uma coluna que contém NULL, as linhas com NULL aparecerão primeiro ou por último, dependendo do mecanismo de banco de dados que estiver usando. Por exemplo, o MySQL coloca NULLs em primeiro lugar para ordenações de ordem ascendente, enquanto o Oracle as coloca em último lugar para ordenações de ordem ascendente. Você pode verificar o comportamento padrão do seu banco de dados na respectiva documentação.

Se você não se lembrar do comportamento padrão ou não gostar dele, poderá usar os operadores NULLS FIRST ou NULLS LAST após ORDER BY para especificar o comportamento desejado:

SELECT 
  title, 
  views
FROM posts
ORDER BY views DESC NULLS LAST;

Isso garante que todas as linhas com um NULL sejam listadas por último:

title	views
Quick Morning Routines!	120,365
…	…
Eco-Friendly Living Tips	256
Easy At-Home Workouts for All Levels
Healthy Snacks on the Go

Você pode ler em detalhes sobre como NULL funciona com ORDER BY em Como ORDER BY e NULL funcionam juntos no SQL.

Como lidar com dados ausentes com NULL no SQL!

O tratamento de NULL e de dados ausentes no SQL é uma habilidade importante para qualquer pessoa que trabalhe com dados. Compreender as nuances do NULL, seu comportamento em diferentes operações e as práticas recomendadas para gerenciar dados ausentes garante que suas consultas sejam precisas e suas análises confiáveis.

Para aprofundar seu conhecimento sobre SQL, considere adquirir nosso pacoteIlimitado Vitalício SQL. Essa oferta de pagamento único fornece acesso vitalício a todos os cursos de SQL atuais e futuros. Os cursos abrangem tudo, desde consultas básicas até SQL avançado; o que você aprender será útil em todos os níveis de sua carreira. Além disso, não deixe de conferir nossa trilhaTrilha de Práticas em SQL com 10 cursos práticos de SQL e mais de 1.000 exercícios. Aprimore suas habilidades a longo prazo com LearnSQL.com.br!