Evitar que joins multipliquem totais no SQL Server

Um relatório une pedidos, itens e pagamentos, agrupa por pedido e retorna uma linha por pedido. O formato parece correto, mas os totais estão errados. GROUP BY pode esconder combinações adicionais sem desfazer seu efeito sobre SUM. A pergunta essencial é o que uma linha de cada entrada representa.

Contar combinações antes da soma

Um pedido com dois itens e três pagamentos gera seis combinações quando ambas as coleções são ligadas independentemente por OrderId. Cada item aparece três vezes e cada pagamento duas. O banco está seguindo exatamente os joins solicitados.

Os dois itens têm propositalmente o mesmo valor no exemplo. Isso também demonstra por que SUM(DISTINCT Amount) não resolve.

DECLARE @Orders TABLE (OrderId int PRIMARY KEY);
DECLARE @Lines TABLE
(LineId int PRIMARY KEY, OrderId int, Amount decimal(12,2));
DECLARE @Payments TABLE
(PaymentId int PRIMARY KEY, OrderId int, Amount decimal(12,2));

INSERT @Orders VALUES (1), (2);
INSERT @Lines VALUES (11, 1, 10), (12, 1, 10);
INSERT @Payments VALUES (21, 1, 5), (22, 1, 7), (23, 1, 8);

SELECT o.OrderId,
       SUM(l.Amount) AS WrongLineTotal,
       SUM(p.Amount) AS WrongPaymentTotal
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId
GROUP BY o.OrderId;

;WITH L AS
(
    SELECT OrderId, SUM(Amount) AS LineTotal
    FROM @Lines GROUP BY OrderId
),
P AS
(
    SELECT OrderId, SUM(Amount) AS PaymentTotal
    FROM @Payments GROUP BY OrderId
)
SELECT o.OrderId,
       COALESCE(l.LineTotal, 0) AS LineTotal,
       COALESCE(p.PaymentTotal, 0) AS PaymentTotal
FROM @Orders AS o
LEFT JOIN L AS l ON l.OrderId = o.OrderId
LEFT JOIN P AS p ON p.OrderId = o.OrderId
ORDER BY o.OrderId;

Para o pedido 1, a primeira consulta informa 60 para itens e 40 para pagamentos. Os valores verdadeiros são 20 e 20. A segunda agrega cada coleção a uma linha por pedido antes do join. O pedido 2 permanece com zeros porque a interpretação adotada considera ausência de itens e pagamentos como zero.

SUM(DISTINCT l.Amount) retornaria 10, não 20. Ele remove valores iguais, não ocorrências adicionais do mesmo item identificado. Dois itens legítimos podem ter o mesmo preço. SELECT DISTINCT no final também não corrige uma soma já calculada sobre linhas multiplicadas.

Ao investigar uma consulta maior, remova temporariamente a agregação e selecione chave pai e chaves primárias filhas. As combinações ficam visíveis. Conte linhas antes e depois de cada join para localizar a primeira mudança de granularidade.

Agregar na granularidade necessária

As CTEs corrigidas garantem no máximo uma linha por OrderId usando GROUP BY. Seus nomes não significam materialização automática; a propriedade lógica da agrupação torna o join seguro. Tabelas derivadas ou expressões APPLY agregadas podem representar a mesma ideia.

OrderId nem sempre define o nível completo. Um relatório por pedido e moeda não pode misturar moedas em um único valor. Um relatório por produto precisa de detalhes já perdidos ao totalizar apenas por pedido. Defina primeiro a chave de saída esperada.

Aplique filtros à medida correta. Se o relatório precisa de todo o valor do pedido e apenas pagamentos liquidados, filtre pagamentos antes da agregação. Uma condição final em WHERE sobre o lado direito pode remover pedidos sem pagamento e transformar efetivamente LEFT JOIN em INNER JOIN.

Quando a tabela filha serve apenas para exigir existência, use EXISTS. Pedir ao menos um pagamento aprovado não significa que cada pagamento deve multiplicar os itens. Expressar essa intenção protege a semântica e frequentemente reduz processamento.

Validar com casos assimétricos

Teste pedido sem filhos, um filho de cada lado, vários itens e um pagamento, e vários filhos nos dois lados. Inclua valores iguais, pagamentos parciais, reembolsos e estados NULL permitidos. Exemplos somente um para um escondem o erro.

COALESCE para zero é uma decisão de negócio. Se a ausência representa informação desconhecida ou importação incompleta, zero pode induzir uma leitura errada. COUNT(*) depois de LEFT JOIN também conta a linha pai preservada sem filho. Conte uma chave filha não NULL para medir filhos reais.

Constraints únicas em dimensões evitam outra multiplicação comum: mapeamentos duplicados em uma tabela supostamente única. Confira a chave verdadeira, incluindo tenant e vigência quando necessário. Uma convenção da aplicação não comprova unicidade.

Depois de estabelecer correção, examine plano real e índices das chaves de agrupamento e join. Pré-agregação pode reduzir intermediários, mas desempenho depende primeiro de uma medida válida. Guarde um conjunto pequeno de regressão com quantidades diferentes de filhos. Ele impedirá que uma simplificação futura reintroduza totais inflados que passam despercebidos em exemplos fáceis.

Referências técnicas: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.