Warum schlägt MySQLs „NOT IN' mit NULL-Werten in mehreren Tabellen fehl und wie kann ich das beheben?

MySQL „NOT IN“-Abfrage über drei Tabellen: Fallstricke und Lösungen

In MySQL wird häufig der „NOT IN“-Operator verwendet Datensätze abrufen, die in einer angegebenen Tabelle nicht vorhanden sind. Bei der Arbeit mit mehreren Tabellen ist es jedoch wichtig, sich der möglichen Fallstricke bewusst zu sein.

Ein Szenario, in dem „NOT IN“ zu unerwarteten Ergebnissen führen kann, ist, wenn die gesuchte Tabelle möglicherweise NULL-Werte enthält. Dieses Problem trat bei einer Abfrage auf, bei der Daten aus den Tabellen „Note“ und „Bewertung“ verglichen wurden. Ziel der Abfrage war es, Datensätze in „note“ zu identifizieren, die mit keinem Eintrag in „evaluation“ übereinstimmten. Es wurden jedoch keine Ergebnisse zurückgegeben, wenn der Name „JOHN“ nicht in beiden Tabellen vorhanden war.

Um dies zu vermeiden Bei diesem Problem wird empfohlen, alternative Methoden wie NOT EXISTS oder Left Joins anstelle von „NOT IN“ zu verwenden. Bei diesen Ansätzen besteht nicht die Möglichkeit, dass NULL-Werte die Abfrage unterbrechen.

Darüber hinaus ist es ratsam, explizite Verknüpfungen anstelle der alten Syntax zu verwenden, die die WHERE-Klausel zum Verknüpfen von Tabellen verwendet. Explizite Verknüpfungen sorgen für mehr Klarheit und vermeiden potenzielle Leistungsprobleme.

Um die Nachteile von „NOT IN“ beim Umgang mit NULL-Werten weiter zu veranschaulichen, betrachten Sie das folgende Beispiel:

Tabellenstruktur:

CREATE TABLE mStatus (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(10) NOT NULL
);
INSERT INTO mStatus (status) VALUES ('single'),('married'),('divorced'),('widow');

CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fullName VARCHAR(100) NOT NULL,
  status VARCHAR(10) NULL
);

Chunk 1:

TRUNCATE TABLE people;
INSERT INTO people (fullName, status) VALUES ('John Henry','single');
SELECT * FROM mStatus WHERE status NOT IN (SELECT status FROM people);

Erwartetes Ergebnis: 3 Reihen (wie erwartet)

Teil 2:

TRUNCATE TABLE people;
INSERT INTO people (fullName, status) VALUES ('John Henry','single'),('Kim Billings',NULL);
SELECT * FROM mStatus WHERE status NOT IN (SELECT status FROM people);

Unerwartetes Ergebnis: Keine Zeilen zurückgegeben

Dieses unerwartete Verhalten entsteht aufgrund der von MySQL verwendeten dreiwertigen Logik. Wenn NULL-Werte beteiligt sind, wird die Abfrage wie folgt übersetzt:

status NOT IN ('single', 'married', 'widowed', NULL)

was äquivalent ist zu:

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

Da der Ausdruck „status=NULL“ als UNBEKANNT ausgewertet wird, wird der gesamte Ausdruck UNBEKANNT, und alle Zeilen werden gefiltert out.

Lösung:

Um dieses Problem zu vermeiden, verwenden Sie alternative Ansätze wie Left Joins oder NOT EXISTS:

SELECT s.status
FROM mStatus s
LEFT JOIN people p ON p.status=s.status
WHERE p.status IS NULL

oder

SELECT s.status
FROM mStatus s
WHERE NOT EXISTS (SELECT 1 FROM people p WHERE p.status=s.status)

Das obige ist der detaillierte Inhalt vonWarum schlägt MySQLs „NOT IN' mit NULL-Werten in mehreren Tabellen fehl und wie kann ich das beheben?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!