Como utilizar operadores de comparação com NULLs em SQL

O valor SQL NULL serve a um propósito especial. Ele também vem com comportamentos contraintuitivos que podem tropeçar em iniciantes SQL ou mesmo programadores experientes. Aprenda como evitar estes problemas quando você usa NULL com operadores de comparação.

Este artigo vai ajudá-lo a dominar as melhores práticas para elaborar consultas SQL que funcionam com valores NULL e usar operadores de comparação ( =, <>, <, > ) - o que, se você já escreveu alguma consulta SQL antes, você sabe que é quase todas as consultas que você vai escrever! Este é um conhecimento essencial e dominá-lo tornará SQL mais fácil para você.

Vamos fazer um rápido tour pelas NULLs SQL, por que elas existem e como sua estranheza afeta os resultados das consultas. Isto será seguido por algumas consultas SQL que demonstram a esquisitice. Em seguida, discutiremos técnicas padrão para escrever consultas que lidam corretamente com as NULLs e operadores de comparação. Finalmente, haverá um resumo rápido de todos os operadores de comparação SQL e como cada um interage com o NULL.

Não se preocupe. Não é tão difícil quanto parece. Vamos começar revendo os valores NULL em SQL.

SQL NULL - O Desconhecido

Os bancos de dados devem ser uma única fonte de verdade. O que é registrado nos campos para cada linha representa o que é conhecido.

Veja a tabela abaixo, que foi adaptada do que é um NULL em SQL (um grande recurso para um mergulho profundo em SQL NULLs). Isto poderia ser parte de um banco de dados criado por um superfan Simpsons.

O NULL representa aqui duas coisas diferentes. Waylon Smithers é conhecido por ser único, portanto o NULL nesta linha na coluna do cônjuge representa um valor inexistente. Mas sabemos tão pouco sobre o Dr. Nick que os valores NULL na coluna do cônjuge e as colunas status representam um valor desconhecido.

Para manter a integridade de um banco de dados, ambas as interpretações do NULL são necessárias. E para ajudar os programadores a manter essa integridade apesar dos valores ausentes e desconhecidos, o SQL incorpora os valores NULL em sua lógica ternária.

O que é lógica ternária e como ela funciona em SQL?

A lógica binária usa dois valores: Verdadeiro e Falso, 0 e 1, etc. A lógica ternária usa três valores. Em SQL, esses três valores são Verdadeiro, Falso e Desconhecido. Em SQL lógica ternária, NULL equivale ao valor do desconhecido.

Eis como os três valores da lógica ternária operam com os operadores lógicos de SQL NOT, OR e AND:

O take-away: Qualquer operação lógica envolvendo um NULL resulta em um valor de desconhecido , exceto VERDADEIRO OU NULO e FALSO E NULO. Interessante, mas não algo que você precisa memorizar. Como você verá, SQL nos ajuda a trabalhar em torno desta lógica complicada. O que é útil, pois as operações de comparação SQL utilizam a mesma lógica ternária.

Operações de comparação SQL e valores NULL

Aqui está uma consulta usando comparação e operadores lógicos. Adivinhe quantas filas ele retorna:

SELECT spouse
  FROM simpsons
 WHERE      spouse = NULL
    OR NOT (spouse = NULL) 

Vamos começar com a primeira operação de comparação:

WHERE      spouse = NULL

O que quer que a coluna de comparação contenha - salários, nomes de animais de estimação, etc. - se testarmos que é igual a NULL, o resultado é desconhecido. Isto é verdade mesmo que o valor da coluna seja NULL. Isto é o que confunde os programadores que são experientes em outros idiomas. Por exemplo, em Python, o valor None parece similar ao SQL NULL e pode ser comparado contra si mesmo:

>>> None == None
True

Mas a primeira linha da cláusula WHERE vai retornar desconhecida. Assim, à medida que nossa consulta for avaliada, ela vai se parecer com isto:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR NOT (spouse = NULL) 

Vamos olhar para a segunda linha da condição WHERE:

   OR NOT (spouse = NULL)
	 

Esta comparação também vai retornar desconhecida. Podemos ver na tabela da verdade mais cedo que NÃO desconhecido vai retornar desconhecido. Assim, agora nossa pergunta se tornou:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR unknown 
	 

A tabela OU da verdade acima nos diz que o resultado disto será desconhecido.

Uma cláusula WHERE requer condições verdadeiras. O resultado não sendo falso não é suficiente. Assim, apesar da consulta parecer que retornará cada linha, a lógica ternária do SQL e a natureza do SQL NULL resulta em linhas zero sendo retornadas.

Outros operadores de comparação da SQL

Para estes exemplos, vamos utilizar uma tabela diferente, pet_owners:

NULL e o < Operador

Usando a tabelapet_owners , vamos ver quem tem menos de 5 animais de estimação.

O resultado:

Por que? Alice, que ainda não completou seu formulário de pesquisa de animais de estimação, tem NULL para ela pet_count. O valor de qualquer NULL é desconhecido. O valor é NULL < 5? Isso é desconhecido, portanto Alice não pode ser incluída nos resultados.

NULL e o > Operador

Agora vamos ver quem tem mais de 3 animais de estimação.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count > 3
	 

O resultado:

Por que? Novamente é o valor desconhecido do NULL. Assim como é desconhecido se é NULL 3 < 5, it is unknown if NULL >. Alice for excluída dos resultados.

NULL e o <= Operador

Em uma mudança sutil, vamos agora obter uma lista de todos com não mais de 5 animais de estimação.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count <= 5
	 

O resultado:

Mudando nossa primeira consulta de < para <= adiciona Bob ao conjunto de resultados, mas não Alice. Na primeira consulta, a contagem de animais de estimação de Bob (5) não é inferior a 5. Mas é menor ou igual a 5, então ele agora está incluído no resultado da consulta. Alice ainda não aparece.

Ao examinar a fila de Alice, podemos pensar em <= como diminutivo para "NULL < 5 OU NULL = 5". Sabemos de cima que "NULL < QUALQUER COISA" retornará desconhecido; de antes, sabemos que "NULL = QUALQUER COISA" também retornará desconhecido. Mais uma vez, Alice é excluída dos resultados.

NULL e o >= Operador

Agora vamos ver quem tem pelo menos 3 animais de estimação.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count >= 3
	 

O resultado:

Como <=, podemos pensar em >= como uma combinação lógica de duas operações de comparação. Assim, para Alice, a comparação é equivalente a "NULL > 3 OU NULL = 3". Agora deve ficar claro que isto só pode resultar em um valor desconhecido.

Operadores de comparação SQL que trabalham com NULLs

Para lidar corretamente com as NULLs, SQL fornece dois operadores especiais de comparação: É NULL e NÃO É NULL. Eles retornam apenas verdadeiros ou falsos e são a melhor prática para incorporar os valores NULL em suas consultas.

Para obter o resultado esperado, podemos reescrever a consulta Simpsons desta forma:

SELECT spouse
  FROM simpsons
 WHERE spouse IS NULL
    OR spouse IS NOT NULL
	 

Agora a consulta retornará a cada fila, como esperávamos.

SQL NULL e Entradas Externas

Este é um tópico mais avançado. Se você é novo em Cláusulas JOIN em SQL, você provavelmente deveria ler primeiro 7 exemplos de SQL JOIN com explicações detalhadas.

É comum usar uma cláusula WHERE junto com o operador IS NOT NULL para se livrar de filas com valores NULL. Mas isto pode às vezes ser uma forma ineficiente de se obter um resultado. Aqui está o porquê.

Junções externas - um LEFT, RIGHT, ou FULL JOIN - podem ser pensadas como um INNER JOIN (que retorna linhas combinadas) mais linhas inigualáveis com colunas preenchidas com NULLs.

Um LEFT JOIN retorna todas as linhas da tabela da esquerda da união com as linhas correspondentes da tabela da direita (ou valores NULL onde não há uma correspondência). Um RIGHT JOIN retorna todas as linhas da tabela da direita com as linhas correspondentes (ou valores NULLs) da tabela da esquerda. Um FULL JOIN é como um INNER JOIN que também retorna todas as linhas da tabela da esquerda e da direita sem igual, estendidas por NULLs.

Se a cláusula WHERE de sua consulta estiver filtrando as linhas que foram estendidas por NULLs, você está basicamente cancelando sua junção externa. Você deve reescrever sua consulta usando um INNER JOIN.

Em outros casos, as NULLs irão interagir com sua cláusula WHERE e causar resultados incorretos. Vejamos um exemplo que demonstra isso. Você mesmo pode executá-lo usando este Fiddle SQL(Aqui está um guia para mais sites a serem usados para a prática SQL).

Para este exemplo, vamos usar duas tabelas, uma para customers e um para orders.

Tabela customers

Tabela orders

Queremos ver o valor gasto por todos os nossos clientes registrados desde 4 de fevereiro de 2021 ("2021-02-04"). Começamos com um INNER JOIN. Há algumas funções SQL extras nesta consulta que podem ser novas para você, mas o LearnSQL já cobriu. Temos artigos sobre COALESCE, funções agregadas como SUM, e a cláusula GROUP BY. Você não precisa se preocupar com o que estas funções fazem neste momento. Basta focar no que cada consulta retorna, começando com esta:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
INNER JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

A consulta produz este resultado:

Parece bom, mas temos 3 clientes. Se quisermos ver cada cliente, precisamos usar um LEFT OUTER JOIN (mais conhecido como LEFT JOIN ). Ela incluirá cada linha na tabela da esquerda da declaração FROM, mesmo que não haja dados correspondentes na tabela da direita. Isto nos dá nossa próxima consulta:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Os resultados podem surpreendê-lo:

Por que isto está acontecendo? Por que a Alice ainda está faltando? Uma consulta mais simples nos dará uma dica:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

O resultado:

Como dito anteriormente, LEFT JOIN inclui todas as filas da tabela da esquerda. Onde não há linha correspondente na tabela da direita, as colunas são preenchidas com NULLs.

A cláusula WHERE realiza sua filtragem após JOIN, então todas as linhas da Alice serão removidas porque a comparação de qualquer coisa com NULL, como um inexistente order_date, retorna desconhecido. WHERE só retorna uma linha onde os condicionais avaliam para VERDADEIRO.

A maneira de corrigir isso é mover a expressão condicional, aqui o.order_date > '2021-02-04', para o JOIN, incluindo-a na cláusula ON:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Como estamos usando um LEFT JOIN, Alice permanece no resultado estabelecido apesar da condição de data adicional. Sua NULL é transformada em um "0" mais limpo pela função COALESCE(). O resultado da consulta é agora o que estávamos esperando:

Saiba mais sobre o SQL NULLs

Agora você deve entender como o SQL trata os valores NULL e as melhores práticas para trabalhar com eles. Você conhece os operadores IS NULL e IS NOT NULL e como a lógica ternária de SQL sempre retorna desconhecida quando algo é comparado a um NULL, exceto em dois casos especiais. Você também já viu como reescrever as consultas para não precisar filtrar aqueles NULLs problemáticos.

Mas há mais a saber sobre como trabalhar com NULLs em SQL. Sugiro que você continue aprendendo com os artigos How ORDER BY and NULL Work Together in SQL and NULL Values and the GROUP BY Clause.

E se você quer realmente dominar SQL, eu recomendo o Curso de Práticas em SQL ou a pista Trilha de Práticas em SQL . Eles oferecem uma experiência de aprendizagem completa e fácil de seguir que o ajudará a aprimorar seu ofício.