Dati · SQL

Perché il totale raddoppia dopo una JOIN in SQL?

Pubblicato il

Un ordine da 30 €, spedito in 2 pacchi: quante righe dà la JOIN tra ordini e pacchi?

Da un ordine spedito in due pacchi a un totale giusto, in cinque tappe:

  1. Trovare l'importo ripetuto
  2. Rifare a mano il totale sbagliato
  3. Contare gli ordini con COUNT(DISTINCT)
  4. Correggere il totale prima della JOIN
  5. Esercitarsi

Perché lo stesso importo compare più volte dopo una JOIN?

Un ordine da 75 € spedito in 3 pacchi compare 3 volte nella JOIN, e ognuna delle sue 3 righe porta 75 €.

Prendiamo tre ordini e i loro pacchi.

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
risultato
orders.idamount
10140 €
10225 €
10225 €
10360 €
  1. Quali pacchi portano il numero dell'ordine 102? Toccali nella tabella.

    Si cerca 102 nella colonna order_id.

  2. La JOIN scrive una riga per ogni coppia ordine-pacco (ecco perché). Ogni riga ricopia l'importo del suo ordine.

    righe

  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

Perché SUM dà un totale troppo alto dopo una JOIN?

Se l'ordine da 75 € compare 3 volte nella JOIN, SUM(amount) lo somma 3 volte: nel totale pesa 225 € invece di 75 €.

Prendiamo le stesse tabelle, 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 €
risultato
SUM(amount)
150 €
  1. Si sommano gli importi della tabella orders, ognuno una sola volta.

    €

  2. SUM somma le righe della JOIN. L'importo di un ordine ci entra una volta per ogni pacco.

    €

  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
    €

Come contare gli ordini senza duplicati dopo una JOIN?

Se 12 ordini spediti danno 20 righe di JOIN, COUNT(*) restituisce 20 e COUNT(DISTINCT orders.id) restituisce 12.

Prendiamo tre ordini, di cui due spediti in più pacchi.

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 €
risultato
COUNT(*)COUNT(DISTINCT orders.id)
63
  1. COUNT(*) conta le righe della JOIN: una per ogni pacco che trova il suo ordine.

  2. DISTINCT tiene ogni numero d'ordine una sola volta, anche se compare su più righe.

  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

Come correggere una SUM gonfiata da una JOIN?

Si riduce la tabella parcels a una riga per ordine prima della JOIN: l'ordine da 90 € spedito in 4 pacchi entra una volta sola nella somma.

Prendiamo tre ordini, di cui due da 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
risultato
SUM(amount)
105 €
  1. SELECT DISTINCT tiene ogni order_id della tabella parcels una sola volta.

    righe

  2. Ogni ordine trova una sola riga in shipped: il suo importo entra una volta sola nella somma.

    €

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

    DISTINCT tiene ogni importo diverso una sola volta, anche se viene da due ordini.

    €

  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
    €

Come riconoscere subito un totale raddoppiato da una JOIN?

Si fa prima contando i pacchi di ogni ordine: è il numero di volte in cui il suo importo entra nella 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
    €