NOT EXISTS x NOT IN: a armadilha do NULL e anti-joins

Voce pode encontrar linhas sem correspondencia em outra tabela de tres formas: NOT IN, NOT EXISTS e LEFT JOIN ... IS NULL. Elas parecem intercambiaveis, mas NOT IN esconde uma armadilha de NULL que um belo dia esvazia o seu resultado sem aviso. Vamos compara-las sobre um esquema users, orders, employees.

O problema: usuarios sem pedidos

O anti-join classico: quem nunca fez um pedido. A versao intuitiva com NOT IN:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Enquanto orders.user_id nao tiver NULL, tudo certo. Mas user_id pode aceitar nulos (compra de convidado, exclusao logica), e ai a consulta quebra: sem erro, sem aviso.

A armadilha do NULL no NOT IN

NOT IN (...) se expande para uma cadeia id <> v1 AND id <> v2 AND .... Se algum valor for NULL, a comparacao id <> NULL nao e TRUE nem FALSE, e sim UNKNOWN. Na logica de tres valores TRUE AND UNKNOWN = UNKNOWN, entao a linha e descartada. Resultado: um unico NULL na subconsulta colapsa todo o resultado para vazio.

-- orders tem uma linha com user_id IS NULL.
-- Isto retorna NADA, mesmo havendo usuarios sem pedidos:
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

• Esse e o comportamento do padrao SQL, nao um bug de um motor especifico.
• NULL IN (...) e NULL NOT IN (...) tambem nunca resultam em TRUE.
• PostgreSQL, MySQL e ClickHouse se comportam da mesma forma.

O paliativo e filtrar o NULL: WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL). Funciona, mas e facil esquecer, e mais ainda em uma coluna dentro de uma chave composta.

NOT EXISTS: seguro por padrao

NOT EXISTS verifica se existe ao menos uma linha e usa logica de existencia de dois valores, entao NULL nao o atrapalha:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Uma linha com o.user_id IS NULL simplesmente nao satisfaz o.user_id = u.id e e ignorada em silencio: nada de colapso. O SELECT 1 interno e um idiom: a lista do select em EXISTS nunca e avaliada, so importa a existencia da linha.

Subconsultas correlacionadas tambem combinam bem com condicoes de varias colunas:

-- Funcionarios sem subordinados (ninguem os lista como manager_id):
SELECT e.id, e.name, e.dept
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees s
    WHERE s.manager_id = e.id
);

LEFT JOIN ... IS NULL

A terceira opcao e um join externo filtrado pela nao correspondencia. Tambem e seguro com NULL como condicao de anti-join:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

• Teste IS NULL na propria chave do join (o.user_id), nao em qualquer coluna nullable de orders.
• Em uma relacao um para muitos o join multiplica linhas, entao depois voce costuma precisar de DISTINCT ou GROUP BY: trabalho extra. O NOT EXISTS evita isso por completo.

Desempenho e planos anti-join

O PostgreSQL moderno executa tanto NOT EXISTS quanto LEFT JOIN ... IS NULL como um verdadeiro anti join (Hash Anti Join / Merge Anti Join no EXPLAIN), parando na primeira correspondencia. Com uma coluna nullable, o planejador nao pode transformar NOT IN em anti-join por causa da semantica de NULL, e costuma sair um plano mais pesado.

• No PostgreSQL prefira NOT EXISTS pela seguranca com NULL e pela velocidade.
• O MySQL 8 tambem otimiza bem NOT EXISTS; historicamente NOT IN podia ser bem mais lento.
• O ClickHouse oferece um LEFT ANTI JOIN limpo: um anti-join explicito sem subconsulta.
• Sustente o predicado correlacionado do NOT EXISTS com um indice (por exemplo em orders(user_id)).

Resumo: torne NOT EXISTS sua escolha padrao para "linhas sem correspondencia". Reserve NOT IN para listas estaticas sem NULL (status NOT IN ('paid','shipped')) e use LEFT JOIN ... IS NULL quando tambem precisar de colunas do lado sem correspondencia.