Warum schlägt MySQLs „NOT IN' mit drei Tabellen und NULL-Werten fehl und was sind die besseren Alternativen?

MySQL „NOT IN“ drei Tabellen abfragen

Wenn Sie mit drei Tabellen arbeiten, verwenden Sie den „NOT IN“-Operator in einer Abfrage kann problematisch sein, insbesondere beim Umgang mit NULL-Werten. Dieser Artikel zeigt die mit der Verwendung von „NOT IN“ verbundenen Risiken auf und schlägt alternative Lösungen vor.

Das Problem

Die bereitgestellte Abfrage versucht, zwei Tabellen, Grade und, zu vergleichen Bewertung: Identifizieren von Datensätzen in der Note, die in der Bewertung nicht vorhanden sind. Wenn der angegebene Name („JOHN“) jedoch in der Auswertung nicht vorhanden ist, gibt die Abfrage keine Ausgabe zurück.

Solution

Um dieses Problem zu beheben Vermeiden Sie die Verwendung von „NOT IN“, wenn die zum Filtern von Daten verwendete Unterabfrage möglicherweise NULL-Werte enthält. Erwägen Sie stattdessen die Verwendung von „NOT EXISTS“ oder Left-Joins.

Lassen Sie uns die potenziellen Gefahren der Verwendung von „NOT IN“ veranschaulichen:

SQL „NICHT IN“ Gefahr

Erstellen Sie die mStatus- und Personentabellen mit Beispiel Daten:

create table mStatus
(   id int auto_increment primary key,
    status varchar(10) not null
);
insert 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
);

Chunk1:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single');
select * from mstatus where `status` not in (select status from people);

Erwartete Ausgabe: 3 Zeilen

Chunk2:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single'),('Kim Billings',null);
select * from mstatus where status not in (select status from people);

Unerwartete Ausgabe: 0 Zeilen

Der zweite Block gibt aufgrund der dreiwertigen Logik von SQL unerwartet keine Zeilen zurück. Wenn die Unterabfrage NULL-Werte enthält, kann der Ausdruck „NOT IN“ als UNKNOWN ausgewertet werden, was dazu führt, dass alle Zeilen herausgefiltert werden.

Alternatives

Um dieses Problem zu beheben Problem, verwenden Sie „LEFT JOIN“ oder „NOT“. EXISTS":

select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null

select s.status
from mstatus s
where not exists (select 1 from people where people.status=s.status)

Diese alternativen Lösungen verarbeiten NULL-Werte korrekt und liefern die gewünschte Ausgabe.

Das obige ist der detaillierte Inhalt vonWarum schlägt MySQLs „NOT IN' mit drei Tabellen und NULL-Werten fehl und was sind die besseren Alternativen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!