Dados · SQL

Por que o total duplica depois de um JOIN no SQL?

Publicado em

Um pedido de R$ 30 foi enviado em 2 pacotes: quantas linhas ele dá no JOIN entre pedidos e pacotes?

De um pedido em dois pacotes a um total certo, em cinco passos:

  1. Achar o valor repetido
  2. Refazer o total errado à mão
  3. Contar os pedidos com COUNT(DISTINCT)
  4. Corrigir o total antes do JOIN
  5. Praticar

Por que o mesmo valor aparece várias vezes depois de um JOIN?

Um pedido de R$ 75 enviado em 3 pacotes aparece 3 vezes no JOIN, e cada uma dessas 3 linhas traz R$ 75.

Vamos pegar três pedidos e os pacotes deles.

SELECT orders.id, amount
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
101R$ 40
102R$ 25
103R$ 60
parcels
idorder_id
501101
502102
503102
504103
resultado
orders.idamount
101R$ 40
102R$ 25
102R$ 25
103R$ 60
  1. Quais pacotes têm o número do pedido 102? Toque neles na tabela.

    Você procura 102 na coluna order_id.

  2. O JOIN escreve uma linha para cada par pedido-pacote (veja por quê). Cada linha copia o valor do seu pedido.

    linhas

  1. SELECT orders.id, amount
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101R$ 35
    102R$ 80
    103R$ 15
    104R$ 50
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104

Por que o SUM dá um total grande demais depois de um JOIN?

Se o pedido de R$ 75 aparece 3 vezes no JOIN, SUM(amount) soma esse valor 3 vezes: ele pesa R$ 225 no total, em vez de R$ 75.

Vamos pegar as mesmas tabelas, com SUM.

SELECT SUM(amount)
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
101R$ 40
102R$ 25
103R$ 60
parcels
idorder_id
501101
502102
503102
504103
join
orders.idamount
101R$ 40
102R$ 25
102R$ 25
103R$ 60
resultado
SUM(amount)
R$ 150
  1. Você soma os valores da tabela orders, cada um uma vez só.

    R$

  2. SUM soma as linhas do JOIN. O valor de um pedido entra uma vez por pacote.

    R$

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101R$ 50
    102R$ 15
    103R$ 70
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    R$

Como contar os pedidos sem duplicar depois de um JOIN?

Se 12 pedidos enviados dão 20 linhas no JOIN, COUNT(*) devolve 20 e COUNT(DISTINCT orders.id) devolve 12.

Vamos pegar três pedidos, dois deles enviados em vários pacotes.

SELECT COUNT(*),
       COUNT(DISTINCT orders.id)
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
101R$ 40
102R$ 25
103R$ 60
parcels
idorder_id
501101
502101
503102
504103
505103
506103
join
orders.idamount
101R$ 40
101R$ 40
102R$ 25
103R$ 60
103R$ 60
103R$ 60
resultado
COUNT(*)COUNT(DISTINCT orders.id)
63
  1. COUNT(*) conta as linhas do JOIN: uma por pacote que encontra o seu pedido.

  2. DISTINCT guarda cada número de pedido uma vez só, não importa quantas linhas ele tenha.

  1. SELECT COUNT(*)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101R$ 15
    102R$ 50
    103R$ 35
    104R$ 70
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104
    507104

Como corrigir um SUM inflado por um JOIN?

Você reduz a tabela parcels a uma linha por pedido antes do JOIN: o pedido de R$ 90 enviado em 4 pacotes entra uma vez só na soma.

Vamos pegar três pedidos, dois deles de R$ 40.

SELECT SUM(amount)
FROM orders
JOIN (SELECT DISTINCT order_id
      FROM parcels) AS shipped
  ON shipped.order_id = orders.id
orders
idamount
101R$ 40
102R$ 25
103R$ 40
parcels
idorder_id
501101
502101
503102
504103
shipped
order_id
101
102
103
resultado
SUM(amount)
R$ 105
  1. SELECT DISTINCT guarda cada order_id da tabela parcels uma vez só.

    linhas

  2. Cada pedido encontra uma única linha em shipped: o valor dele entra uma vez na soma.

    R$

  3. SELECT SUM(DISTINCT amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    resultado
    SUM(DISTINCT amount)
    R$ 65

    DISTINCT guarda cada valor diferente uma vez só, mesmo que ele venha de dois pedidos.

    R$

  1. SELECT SUM(amount)
    FROM orders
    JOIN (SELECT DISTINCT order_id
          FROM parcels) AS shipped
      ON shipped.order_id = orders.id
    orders
    idamount
    101R$ 35
    102R$ 50
    103R$ 20
    104R$ 70
    parcels
    idorder_id
    501101
    502101
    503103
    504104
    505104
    506104
    R$

Como achar rápido um total duplicado por um JOIN?

Você ganha velocidade quando conta primeiro os pacotes de cada pedido: é o número de vezes que o valor dele entra no JOIN com parcels.

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101R$ 25
    102R$ 60
    103R$ 15
    104R$ 80
    parcels
    idorder_id
    501101
    502102
    503102
    504103
    R$