So lösen Sie das Problem des ungültigen linken Joins in MySQL und die Vorsichtsmaßnahmen bei seiner Verwendung

Mysql Left Join ist ungültig und wie man es verwendet

Als ich heute SQL schrieb, stellte ich fest, dass mit dem Left Join nicht alle Daten aus der linken Tabelle abgerufen wurden, was mich eine Weile deprimierte. Später, nach sorgfältigem Studium, wurde mir klar, dass ich einen Fehler gemacht hatte, der dem gesunden Menschenverstand entsprach (ich bin ein Anfänger).

Dies ist das Original-SQL

Diese Abfrage kann nicht alle Daten in der Tabelle tb_line abrufen, was peinlich ist …

Später erfuhr ich, dass bei Abfragen mehrerer Tabellen vor der Ausführung der Where-Anweisung eine temporäre Tabelle erstellt wird.

Ein ist die bedingte Filterung in der temporären Tabelle. Bei Verwendung des Left Join werden die Daten in der linken Tabelle abgefragt, unabhängig davon, ob die Bedingung erfüllt ist oder nicht. Wenn die Bedingung falsch ist, werden sie als Null angezeigt.

wo ist die Filterbedingung nach der Generierung der temporären Tabelle

In der ersten Abbildung habe ich die Filterbedingung der Tabelle tb_vehicle in die Where-Klausel eingefügt. Die durch den Left Join generierten Daten mit falscher Bedingung werden in der Where-Klausel v.del_flag='0' herausgefiltert (weil das del_flag bei Daten mit falscher Bedingung leer ist).

Es sieht also so aus, als hätte ich einen Left Join verwendet, aber tatsächlich ist das Ergebnis dieser Schreibweise dasselbe wie bei Verwendung eines Inner Join.

Das korrekte SQL lautet wie folgt:

In der temporären Tabelle können wir die Bedingungen filtern, sodass wir die Daten in der linken Tabelle erhalten.

Zusammenfassen:

Wenn Sie einen Left Join verwenden und eine bedingte Abfrage durchführen müssen, müssen Sie sorgfältig überlegen, ob Sie den bedingten Filter nach „on“ oder nach „where“ einfügen.

Leitfaden zur Vermeidung von Fallstricken bei MySQL-Left-Joins

Phänomen

LEFT JOIN kommt sehr häufig vor, wenn wir MySQL für Abfragen verwenden, etwa wie viele Kommentare es zu einem Artikel in einem Blog gibt, wie viele Kommentare es zu einem Produkt in einem Einkaufszentrum gibt, wie viele Likes es zu einem Kommentar gibt usw. Aufgrund der Unbekanntheit von Schlüsselwörtern wie „join“, „on“, „where“ usw. entsprechen die Abfrageergebnisse jedoch manchmal nicht den Erwartungen. Deshalb werde ich sie heute zusammenfassen und Ihnen helfen, Fallstricke zu vermeiden.

Hier werde ich ein Szenario beschreiben und zwei Fragen stellen. Wenn Sie diese richtig beantworten können, müssen Sie diesen Artikel nicht lesen.

Angenommen, es gibt eine Klassenverwaltungsanwendung. Es gibt eine Tabelle „classes“, in der alle Klassen gespeichert sind. Es gibt eine Tabelle „students“, in der alle Schüler gespeichert sind. Die spezifischen Daten sind wie folgt:

SELECT * FROM Klassen;

SELECT * FROM Studenten;

Nun gibt es zwei Voraussetzungen:

Finden Sie den Namen jeder Klasse und die Anzahl der dazugehörigen Schülerinnen

Ermitteln Sie die Gesamtzahl der Schüler in einer Klasse

Für Anforderung 1 fallen den meisten Leuten spontan die folgenden zwei Möglichkeiten zum Schreiben von SQL ein. Welche ist richtig?

WÄHLEN Sie c.name, count(s.name) als Nummer 
    VON Klassen c links beitreten Studenten s 
    auf s.class_id = c.id 
    und s.gender = "F"
    Gruppieren nach c.name

oder

WÄHLEN Sie c.name, count(s.name) als Nummer 
    VON Klassen c links beitreten Studenten s 
    auf s.class_id = c.id 
    wobei s.gender = "F"
    Gruppieren nach c.name

Für Anforderung 2 fallen den meisten Leuten ohne nachzudenken die folgenden zwei Möglichkeiten zum Schreiben von SQL ein. Welche ist richtig?

WÄHLEN Sie c.name, count(s.name) als Nummer 
    VON Klassen c links beitreten Studenten s 
    auf s.class_id = c.id 
    wobei c.name = "Klasse 1" 
    Gruppieren nach c.name

oder

WÄHLEN Sie c.name, count(s.name) als Nummer 
    VON Klassen c links beitreten Studenten s 
    auf s.class_id = c.id 
    und c.name = 'Klasse 1' 
    Gruppieren nach c.name

Bitte nicht nach unten scrollen! ! Geben Sie zunächst Ihre eigene Antwort, die richtige Antwort steht weiter unten.

~

~

~

Die Antwort ist, dass die erste Aussage für beide Anforderungen richtig ist . Um dieses Problem zu verstehen, müssen Sie das Ausführungsprinzip von MySQL für Left Join verstehen, das im nächsten Abschnitt näher erläutert wird.

Quelle

MySQL verwendet einen Nested-Loop-Ansatz, um Left Joins zu verarbeiten. Nehmen Sie als Beispiel die folgende Anweisung:

Wählen Sie * von LT links aus, verbinden Sie RT auf P1 (LT, RT)), wobei P2 (LT, RT)

P1 ist die Filterbedingung „on“, die als WAHR betrachtet wird, wenn sie fehlt. P2 ist die Filterbedingung „where“, die ebenfalls als WAHR betrachtet wird, wenn sie fehlt. Die Ausführungslogik dieser Anweisung kann wie folgt beschrieben werden:

FÜR jede Zeile lt in LT {//Jede Zeile der linken Tabelle durchlaufen BOOL b = FALSE;
  FÜR jede Zeile rt in RT, sodass P1(lt, rt) {// Jede Zeile der rechten Tabelle durchlaufen und die Zeile finden, die die Verknüpfungsbedingung erfüllt, WENN P2(lt, rt) {// Die Where-Filterbedingung erfüllen t:=lt||rt;// Die Zeile zusammenführen und die Zeile ausgeben}
    b=TRUE; // es hat eine entsprechende Zeile in RT}
  IF (!b) { // Wenn nach dem Durchlaufen von RT keine entsprechende Zeile für lt in RT vorhanden ist, versuche, eine Zeile mit Null zu füllen. IF P2(lt,NULL) {// Nach dem Füllen von Null ist die Where-Filterbedingung t:=lt||NULL erfüllt; // Gib die mit lt und Null gefüllte Zeile aus. }         
  }
}

Natürlich verwendet MySQL in tatsächlichen Situationen Puffer zur Optimierung und reduziert die Anzahl der Zeilenvergleiche, dies wirkt sich jedoch nicht auf den Schlüsselausführungsprozess aus und liegt nicht im Rahmen dieses Artikels.

Aus diesem Pseudocode können wir zwei Punkte erkennen:

Wenn Sie die rechte Tabelle einschränken möchten, müssen Sie dies in der Bedingung „on“ tun. Wenn Sie dies in der Bedingung „where“ tun, kann dies zu Datenverlusten führen und dazu, dass die Zeilen in der linken Tabelle, die keine übereinstimmenden Zeilen in der rechten Tabelle haben, nicht im Endergebnis erscheinen, was unserem Verständnis von Left Join widerspricht. Da für die Zeilen in der linken Tabelle, die in der rechten Tabelle keine übereinstimmenden Zeilen haben, b=FALSE nach dem Durchlaufen der rechten Tabelle ist, werden wir versuchen, die rechte Tabelle mit NULL zu füllen. Zu diesem Zeitpunkt schränkt unser P2 jedoch die Zeilen in der rechten Tabelle ein. Wenn NULL P2 nicht erfüllt (NULL erfüllt im Allgemeinen die Einschränkungsbedingungen nicht, es sei denn, es ist IS NULL), wird es nicht zum Endergebnis hinzugefügt, was zu fehlenden Ergebnissen führt.

Wenn keine Where-Bedingung vorhanden ist, wird jede Zeile der linken Tabelle unabhängig von den Einschränkungen, die die On-Bedingung der linken Tabelle auferlegt, mindestens eine Zeile mit synthetischen Ergebnissen enthalten. Wenn für eine Zeile der linken Tabelle keine entsprechende Zeile in der rechten Tabelle vorhanden ist, ist nach Abschluss der Durchquerung der rechten Tabelle b=FALSE und eine Zeile mit NULL wird zum Generieren von Daten verwendet, was redundant ist. Daher muss where verwendet werden, um die linke Tabelle zu filtern.

Nachfolgend sind die Ausführungsergebnisse und Fehlerursachen der Fehleranweisungen für die beiden Anforderungen aufgeführt:

Anforderung 1

Anforderung 2

Anforderung 1: Aufgrund der Einschränkung auf die rechte Tabelle in der Where-Bedingung fehlen Daten (das Ergebnis der Klasse 4 sollte 0 sein)

Anforderung 2 enthält redundante Daten aufgrund der Einschränkung auf die linke Tabelle in der Ein-Bedingung (die Ergebnisse anderer Klassen sind ebenfalls verfügbar, aber sie sind immer noch falsch).

Zusammenfassen

Aus dem oben beschriebenen Problemphänomen und seiner Analyse können wir folgende Schlussfolgerung ziehen: In der linken Join-Anweisung muss der linke Tabellenfilter in die Where-Bedingung und der rechte Tabellenfilter in die On-Bedingung gesetzt werden, damit das Ergebnis weder zu viel noch zu wenig, sondern genau richtig ist.

SQL mag einfach erscheinen, enthält aber tatsächlich viele Details und Prinzipien. Eine kleine Verwirrung kann dazu führen, dass die Ergebnisse nicht den Erwartungen entsprechen. Daher sollten Sie auf diese Details und Prinzipien achten, um in kritischen Momenten keine Fehler zu machen.

Das Obige ist meine persönliche Erfahrung. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.