NULL e a lógica de três valores

Em SQL, uma condição pode dar verdadeiro, falso ou desconhecido (NULL). Qualquer comparação com NULL, inclusive NULL = NULL, dá desconhecido. O WHERE e o ON só deixam passar o que é verdadeiro.

SELECT NULL = NULL AS igual,
       NULL <> 1 AS diferente,
       NULL IS NULL AS e_nulo,
       1 + NULL AS soma,
       'a' || NULL AS concatena;

igual | diferente | e_nulo | soma | concatena
----- | --------- | ------ | ---- | ---------
NULL  | NULL      | 1      | NULL | NULL

IS NULL, nunca = NULL

SELECT
  (SELECT COUNT(*) FROM clientes WHERE cidade = NULL) AS com_igual,
  (SELECT COUNT(*) FROM clientes WHERE cidade IS NULL) AS com_is_null;

com_igual | com_is_null
--------- | -----------
0         | 1

A armadilha do NOT IN

x NOT IN (a, b, NULL) é x <> a AND x <> b AND x <> NULL. O último termo é desconhecido, então o todo nunca é verdadeiro: a consulta volta vazia. Com NOT EXISTS isso não acontece.

SELECT
  (SELECT COUNT(*) FROM clientes WHERE cidade NOT IN ('Recife', NULL)) AS not_in,
  (SELECT COUNT(*) FROM clientes c
    WHERE NOT EXISTS (SELECT 1 FROM (SELECT 'Recife' AS x UNION ALL SELECT NULL) l WHERE l.x = c.cidade)) AS not_exists;

not_in | not_exists
------ | ----------
0      | 5

O NOT EXISTS devolveu 5: os quatro clientes com cidade e a Elisa, cuja cidade é desconhecida. Decida se ela entra; o que não dá é a resposta mudar porque a lista ganhou um NULL.

AND e OR com desconhecido

• FALSE AND NULL é falso (já se sabe a resposta); TRUE AND NULL é desconhecido.
• TRUE OR NULL é verdadeiro; FALSE OR NULL é desconhecido.
• NOT NULL continua desconhecido.

SELECT (1 = 0) AND NULL AS falso_e_nulo,
       (1 = 1) OR NULL AS verdadeiro_ou_nulo,
       (1 = 1) AND NULL AS verdadeiro_e_nulo;

falso_e_nulo | verdadeiro_ou_nulo | verdadeiro_e_nulo
------------ | ------------------ | -----------------
0            | 1                  | NULL

(O SQLite mostra verdadeiro como 1 e falso como 0.)

COALESCE e a comparação que trata NULL como valor

COALESCE(a, b, …) devolve o primeiro que não é NULL. Para comparar tratando dois NULL como iguais, o padrão é IS NOT DISTINCT FROM; no SQLite, IS; no Spark, o operador <=>.

SELECT nome, COALESCE(cidade, '(sem cidade)') AS cidade,
       cidade IS NULL AS desconhecida
FROM clientes
WHERE id IN (4, 5);

nome  | cidade       | desconhecida
----- | ------------ | ------------
Diego | Curitiba     | 0
Elisa | (sem cidade) | 1

• No ORDER BY ... ASC, o SQLite e o Spark põem NULL primeiro; o PostgreSQL, por último. Escreva NULLS FIRST ou NULLS LAST quando a ordem importar.
• NULL em chave de JOIN nunca casa com nada, nem com outro NULL.