Daten · SQL

Warum macht WHERE aus einem LEFT JOIN einen INNER JOIN?

Veröffentlicht am

Vier Bestellungen über 40, 60, 20 und 50 €: Wie viele erfüllen amount > 30?

Von einem Filter auf Beträge bis zu zwei Versionen derselben Abfrage, in fünf Etappen:

  1. Die Zeilen eines Joins mit WHERE filtern
  2. Die NULL-Zeile bis zum WHERE verfolgen
  3. Jeden Kunden mit einer Bedingung in ON behalten
  4. Die Zeilen beider Versionen zählen
  5. Üben

Wann wird WHERE bei einem Join ausgeführt?

WHERE kommt nach dem Join und prüft jede Zeile seines Ergebnisses. Eine Bestellung über 12 € steht noch im Ergebnis des Joins, dann entfernt WHERE amount > 30 ihre Zeile.

Nehmen wir drei Kunden und ihre Bestellungen.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Anna
2Ben
3Mia
orders
idcustomer_idamount
101140 €
102170 €
103225 €
104260 €
105350 €
LEFT JOIN, vor dem WHERE
nameamount
Anna40 €
Anna70 €
Ben25 €
Ben60 €
Mia50 €
Ergebnis
nameamount
Anna40 €
Anna70 €
Ben60 €
Mia50 €
  1. Jeder Kunde steht einmal pro Bestellung darin, wie bei jedem Join.

    Zeilen

  2. Welche Zeilen erfüllen amount > 30? Tippe sie im Join an.

    Du liest den Betrag jeder Zeile und vergleichst ihn mit 30.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Anna
    2Ben
    3Mia
    orders
    idcustomer_idamount
    101135 €
    102165 €
    103220 €
    104245 €
    105370 €

Warum entfernt WHERE die NULL-Zeilen eines LEFT JOIN?

Der Kunde mit der id 7 hat keine Bestellung: In seiner Zeile ist der Betrag NULL. NULL > 0 ergibt nicht wahr, also entfernt WHERE amount > 0 seine Zeile.

Nehmen wir drei Kunden, einer davon ohne Bestellung.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Anna
2Ben
3Mia
orders
idcustomer_idamount
101140 €
102360 €
LEFT JOIN, vor dem WHERE
nameamount
Anna40 €
BenNULL
Mia60 €
Ergebnis
nameamount
Anna40 €
Mia60 €
  1. LEFT JOIN behält jeden Kunden, auch ohne Bestellung.

    Zeilen

  2. Welche Zeilen erfüllen amount > 30?

    Du vergleichst jeden Betrag mit 30. In Bens Zeile vergleichst du NULL mit 30.

  3. Welcher Join liefert ohne WHERE genau diese Zeilen?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Anna
    2Ben
    3Mia
    4Paul
    orders
    idcustomer_idamount
    101145 €
    102320 €
    103365 €
    104435 €

Wie filtert man einen LEFT JOIN, ohne Kunden zu verlieren?

Steht die Bedingung in ON, wählt sie die Bestellungen aus, die verbunden werden. Danach behält LEFT JOIN jeden Kunden. Der Kunde mit der id 7 hat nur eine Bestellung über 12 €: AND amount > 30 lässt sie nicht durch, und er steht trotzdem im Ergebnis, mit dem Betrag NULL.

Nehmen wir drei Kunden, mit der Bedingung in ON.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Anna
2Ben
3Mia
orders
idcustomer_idamount
101140 €
102160 €
103125 €
104320 €
Ergebnis
nameamount
Anna40 €
Anna60 €
BenNULL
MiaNULL
  1. Was steht in Mias Zeile in der Spalte amount?

    Du prüfst zuerst, welche von Mias Bestellungen amount > 30 erfüllen.

  2. Jeder Kunde steht einmal pro verbundener Bestellung im Ergebnis, und einmal, wenn er keine hat.

    Zeilen

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
      AND orders.amount > 40
    customers
    idname
    1Anna
    2Ben
    3Mia
    4Paul
    orders
    idcustomer_idamount
    101150 €
    102165 €
    103215 €
    104245 €
    105320 €
    106435 €

Was ist der Unterschied zwischen einer Bedingung in ON und in WHERE?

Bei 7 Kunden liefert die Bedingung in ON mindestens 7 Zeilen, mindestens eine pro Kunde. In WHERE behält sie nur die Zeilen, in denen sie wahr ergibt, und kann auch alle entfernen.

Nehmen wir vier Kunden und beide Versionen der Abfrage.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Anna
2Ben
3Mia
4Paul
orders
idcustomer_idamount
101170 €
102145 €
103120 €
104235 €
105415 €
Ergebnis mit WHERE
nameamount
Anna70 €
Anna45 €
Ben35 €
Ergebnis mit ON
nameamount
Anna70 €
Anna45 €
Ben35 €
MiaNULL
PaulNULL
  1. WHERE prüft jede Zeile des LEFT JOIN. In Mias Zeile ist der Betrag NULL.

    Zeilen

  2. Jeder Kunde steht einmal pro Bestellung mit amount > 30 im Ergebnis, und einmal, wenn er keine solche Bestellung hat.

    Zeilen

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Anna
2Ben
3Mia
4Paul
orders
idcustomer_idamount
101125 €
102155 €
103340 €
104365 €
105350 €
106420 €

Wie liest man einen gefilterten LEFT JOIN schnell?

Schneller geht es, wenn du zuerst schaust, wo die Bedingung steht. In WHERE zählst du die Bestellungen, die sie erfüllen. In ON steht jeder Kunde einmal pro Bestellung, die sie erfüllt, im Ergebnis, und einmal, wenn er keine solche hat.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Anna
    2Ben
    3Mia
    orders
    idcustomer_idamount
    101135 €
    102360 €
    103315 €