EXCEPT no SQL: diferenca de conjuntos e o equivalente MINUS do Oracle

EXCEPT retorna as linhas da primeira consulta que nao estao na segunda: e a diferenca de conjuntos na sua forma mais pura. O Oracle chama esse mesmo operador de MINUS; a sintaxe e o significado sao identicos.

O que o EXCEPT faz

Pegue todos os usuarios e subtraia os que foram banidos. Restam exatamente os user_id presentes no primeiro conjunto, mas ausentes no segundo.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Assim como o UNION, o operador exige o mesmo numero de colunas e tipos compativeis em ambos os ramos. A comparacao e feita sobre a linha inteira, nao apenas sobre a primeira coluna. Um detalhe importante: o EXCEPT trata NULL como um unico valor, entao uma linha com NULL a esquerda e cancelada por uma linha com NULL a direita, algo que um = comum nunca faz.

• Os nomes das colunas vem do primeiro SELECT.
• O resultado nao contem duplicatas: o EXCEPT aplica um DISTINCT implicito.
• A ordem das linhas nao e garantida, entao adicione ORDER BY bem no fim se precisar de uma.

EXCEPT versus EXCEPT ALL

O EXCEPT comum colapsa as repeticoes: mesmo que um valor apareca dez vezes a esquerda, no resultado ele figura no maximo uma vez. O EXCEPT ALL mantem a multiplicidade: de N ocorrencias a esquerda subtrai M a direita e deixa max(N - M, 0) copias.

-- DISTINCT difference: each surviving id appears once
SELECT user_id FROM orders
EXCEPT
SELECT user_id FROM orders WHERE status = 'refunded';

-- Multiset difference: per-row counts are subtracted
SELECT user_id FROM orders
EXCEPT ALL
SELECT user_id FROM orders WHERE status = 'refunded';

Pegadinha: o EXCEPT ALL existe no PostgreSQL, mas nao no MySQL (que nao tinha EXCEPT antes da 8.0.31), e o ClickHouse tem sua propria semantica de duplicatas. Nao presuma que o comportamento de multiconjunto e o padrao; confirme qual variante o seu motor realmente suporta.

EXCEPT versus NOT EXISTS e LEFT JOIN

A mesma diferenca costuma ser escrita com NOT EXISTS ou LEFT JOIN ... IS NULL. Eles nao sao sinonimos exatos, e a forma como cada um lida com NULL e duplicatas decide qual escolher.

-- Set difference via NOT EXISTS
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM banned b WHERE b.user_id = u.user_id
);

-- Same idea via anti-join
SELECT u.user_id
FROM users u
LEFT JOIN banned b ON b.user_id = u.user_id
WHERE b.user_id IS NULL;

As diferencas principais:

• O EXCEPT remove sozinho as duplicatas do lado esquerdo; NOT EXISTS e LEFT JOIN as mantem, a menos que voce adicione um DISTINCT explicito.
• O EXCEPT compara a linha inteira em todas as colunas, ao passo que o NOT EXISTS so verifica as condicoes que voce escreve no seu WHERE.
• O NOT EXISTS lida corretamente com NULL na tabela da direita; o classico NOT IN quebra com NULL e retorna vazio em silencio. Para uma unica coluna, essa e a armadilha mais comum.
• Quando e preciso reconciliar varias colunas de uma vez, o EXCEPT fica mais legivel: SELECT a, b, c ... EXCEPT SELECT a, b, c ... supera um NOT EXISTS prolixo com tres condicoes.

Em desempenho, o NOT EXISTS costuma vencer em tabelas grandes: o planejador o converte em um hash anti join e nunca materializa por completo os dois ramos so para deduplicar. O EXCEPT, por sua vez, primeiro monta os dois conjuntos e aplica hash, o que consome mais memoria.

Reconciliacao por meio da diferenca

O papel pratico principal do EXCEPT e a reconciliacao bidirecional. Para encontrar divergencias entre uma origem e um destino, calcule a diferenca nos dois sentidos.

-- Rows in source but missing in target
SELECT id, user_id, amount FROM orders_source
EXCEPT
SELECT id, user_id, amount FROM orders_target;

Se a consulta nao retornar nada, a origem esta totalmente contida no destino. A verificacao simetrica troca os ramos, ou voce combina as duas diferencas com UNION ALL mais um rotulo de direcao. Isso e bem mais confiavel do que comparar apenas COUNT(*): o mesmo numero de linhas ainda nao garante que as linhas coincidam.

Resumindo: o EXCEPT e uma forma concisa de expressar uma diferenca sobre todo o conjunto de colunas com deduplicacao automatica, ideal para reconciliacoes. Quando a velocidade em grandes volumes importa ou voce precisa preservar duplicatas, recorra ao NOT EXISTS.