Datos · SQL

¿Por qué se duplica el total después de un JOIN en SQL?

Publicado el

Un pedido de 30 €, enviado en 2 paquetes: ¿cuántas filas da el JOIN entre pedidos y paquetes?

De un pedido en dos paquetes a un total correcto, en cinco pasos:

  1. Localizar el importe repetido
  2. Rehacer a mano el total erróneo
  3. Contar los pedidos con COUNT(DISTINCT)
  4. Corregir el total antes del JOIN
  5. Practicar

¿Por qué sale el mismo importe varias veces después de un JOIN?

Un pedido de 75 € enviado en 3 paquetes sale 3 veces en el JOIN, y cada una de sus 3 filas lleva 75 €.

Tomemos tres pedidos y sus paquetes.

SELECT orders.id, amount
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
10140 €
10225 €
10360 €
parcels
idorder_id
501101
502102
503102
504103
resultado
orders.idamount
10140 €
10225 €
10225 €
10360 €
  1. ¿Qué paquetes llevan el número del pedido 102? Los tocamos en la tabla.

    Buscamos 102 en la columna order_id.

  2. El JOIN escribe una fila por cada pareja pedido-paquete (ver por qué). Cada fila copia el importe de su pedido.

    filas

  1. SELECT orders.id, amount
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    10135 €
    10280 €
    10315 €
    10450 €
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104

¿Por qué SUM da un total demasiado grande después de un JOIN?

Si el pedido de 75 € sale 3 veces en el JOIN, SUM(amount) lo suma 3 veces: aporta 225 € al total en lugar de 75 €.

Tomemos las mismas tablas, con SUM.

SELECT SUM(amount)
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
10140 €
10225 €
10360 €
parcels
idorder_id
501101
502102
503102
504103
join
orders.idamount
10140 €
10225 €
10225 €
10360 €
resultado
SUM(amount)
150 €
  1. Sumamos los importes de la tabla orders, cada uno una sola vez.

    €

  2. SUM suma las filas del JOIN. El importe de un pedido entra en ellas una vez por cada paquete.

    €

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    10150 €
    10215 €
    10370 €
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    €

¿Cómo contar los pedidos sin duplicados después de un JOIN?

Si 12 pedidos enviados dan 20 filas en el JOIN, COUNT(*) devuelve 20 y COUNT(DISTINCT orders.id) devuelve 12.

Tomemos tres pedidos, dos de ellos enviados en varios paquetes.

SELECT COUNT(*),
       COUNT(DISTINCT orders.id)
FROM orders
JOIN parcels
  ON parcels.order_id = orders.id
orders
idamount
10140 €
10225 €
10360 €
parcels
idorder_id
501101
502101
503102
504103
505103
506103
join
orders.idamount
10140 €
10140 €
10225 €
10360 €
10360 €
10360 €
resultado
COUNT(*)COUNT(DISTINCT orders.id)
63
  1. COUNT(*) cuenta las filas del JOIN: una por cada paquete que encuentra su pedido.

  2. DISTINCT toma cada número de pedido una sola vez, tenga las filas que tenga.

  1. SELECT COUNT(*)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    10115 €
    10250 €
    10335 €
    10470 €
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104
    507104

¿Cómo corregir un SUM inflado por un JOIN?

Se reduce la tabla parcels a una fila por pedido antes del JOIN: así, el pedido de 90 € enviado en 4 paquetes entra una sola vez en la suma.

Tomemos tres pedidos, dos de ellos de 40 €.

SELECT SUM(amount)
FROM orders
JOIN (SELECT DISTINCT order_id
      FROM parcels) AS shipped
  ON shipped.order_id = orders.id
orders
idamount
10140 €
10225 €
10340 €
parcels
idorder_id
501101
502101
503102
504103
shipped
order_id
101
102
103
resultado
SUM(amount)
105 €
  1. SELECT DISTINCT toma cada order_id de la tabla parcels una sola vez.

    filas

  2. Cada pedido encuentra una sola fila en shipped: su importe entra una vez en la suma.

    €

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

    DISTINCT toma cada importe distinto una sola vez, aunque venga de dos pedidos.

    €

  1. SELECT SUM(amount)
    FROM orders
    JOIN (SELECT DISTINCT order_id
          FROM parcels) AS shipped
      ON shipped.order_id = orders.id
    orders
    idamount
    10135 €
    10250 €
    10320 €
    10470 €
    parcels
    idorder_id
    501101
    502101
    503103
    504104
    505104
    506104
    €

¿Cómo detectar rápido un total duplicado por un JOIN?

Se va más rápido contando primero los paquetes de cada pedido: es el número de veces que su importe entra en el JOIN con parcels.

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    10125 €
    10260 €
    10315 €
    10480 €
    parcels
    idorder_id
    501101
    502102
    503102
    504103
    €