Daten · SQL

Warum verdoppelt ein SQL-Join die Summe?

Veröffentlicht am

Eine Bestellung über 30 €, verschickt in 2 Paketen: Wie viele Zeilen liefert der Join von Bestellungen und Paketen?

Von einer Bestellung in zwei Paketen bis zur richtigen Summe, in fünf Etappen:

  1. Den wiederholten Betrag erkennen
  2. Die falsche Summe von Hand nachrechnen
  3. Bestellungen mit COUNT(DISTINCT) zählen
  4. Die Summe vor dem Join korrigieren
  5. Üben

Warum steht nach einem Join derselbe Betrag mehrmals da?

Eine Bestellung über 75 €, verschickt in 3 Paketen, steht 3-mal im Join, und jede ihrer 3 Zeilen trägt 75 €.

Nehmen wir drei Bestellungen und ihre Pakete.

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
Ergebnis
orders.idamount
10140 €
10225 €
10225 €
10360 €
  1. Welche Pakete tragen die Nummer der Bestellung 102? Tippe sie in der Tabelle an.

    Du suchst die 102 in der Spalte order_id.

  2. Der Join schreibt eine Zeile pro Paar aus Bestellung und Paket (warum das so ist). Jede Zeile übernimmt den Betrag ihrer Bestellung.

    Zeilen

  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

Warum liefert SUM nach einem Join eine zu große Summe?

Steht die Bestellung über 75 € 3-mal im Join, addiert SUM(amount) sie 3-mal: Sie zählt in der Summe 225 € statt 75 €.

Nehmen wir dieselben Tabellen, mit 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 €
Ergebnis
SUM(amount)
150 €
  1. Du addierst die Beträge der Tabelle orders, jeden nur einmal.

    €

  2. SUM addiert die Zeilen des Joins. Der Betrag einer Bestellung kommt dort einmal pro Paket vor.

    €

  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
    €

Wie zählt man Bestellungen nach einem Join ohne Duplikate?

Ergeben 12 verschickte Bestellungen 20 Zeilen im Join, liefert COUNT(*) 20 und COUNT(DISTINCT orders.id) 12.

Nehmen wir drei Bestellungen, zwei davon in mehreren Paketen.

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 €
Ergebnis
COUNT(*)COUNT(DISTINCT orders.id)
63
  1. COUNT(*) zählt die Zeilen des Joins: eine pro Paket, das seine Bestellung findet.

  2. DISTINCT behält jede Bestellnummer nur einmal, egal in wie vielen Zeilen sie steht.

  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

Wie korrigiert man ein zu großes SUM nach einem Join?

Du verkleinerst die Tabelle parcels vor dem Join auf eine Zeile pro Bestellung: Die Bestellung über 90 € in 4 Paketen kommt dann nur einmal in die Summe.

Nehmen wir drei Bestellungen, zwei davon über 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
Ergebnis
SUM(amount)
105 €
  1. SELECT DISTINCT behält jede order_id der Tabelle parcels nur einmal.

    Zeilen

  2. Jede Bestellung findet eine einzige Zeile in shipped: Ihr Betrag kommt einmal in die Summe.

    €

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

    DISTINCT nimmt jeden Betrag nur einmal, auch wenn er von zwei Bestellungen kommt.

    €

  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
    €

Wie erkennt man schnell eine vom Join verdoppelte Summe?

Schneller geht es, wenn du zuerst die Pakete jeder Bestellung zählst: So oft steht ihr Betrag im Join mit 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
    €