Pourquoi 'NOT IN' de MySQL échoue-t-il avec trois tables et des valeurs NULL, et quelles sont les meilleures alternatives ?

MySQL « NOT IN » Interrogation de trois tables

Lorsque vous travaillez avec trois tables, utilisez l'opérateur « NOT IN » dans une requête peut être problématique, surtout lorsqu'il s'agit de valeurs NULL. Cet article démontre les risques associés à l'utilisation de "NOT IN" et suggère des solutions alternatives.

Le problème

La requête fournie cherche à comparer deux tables, Grade et Évaluation, identifiant les enregistrements dans la note qui ne sont pas présents dans l'évaluation. Cependant, lorsque le nom spécifié (« JOHN ») n'existe pas dans l'évaluation, la requête ne renvoie aucun résultat.

Solution

Pour résoudre ce problème , évitez d'utiliser "NOT IN" si la sous-requête utilisée pour filtrer les données peut contenir des valeurs NULL. Envisagez plutôt d'utiliser "NOT EXISTS" ou des jointures à gauche.

Illustrons les dangers potentiels liés à l'utilisation de "NOT IN":

SQL "PAS DANS" Danger

Créez le mStatus et tables de personnes avec des exemples de données :

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);

Sortie attendue : 3 lignes

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);

Sortie inattendue : 0 lignes

Le deuxième morceau ne renvoie aucune ligne de manière inattendue à cause de La logique à trois valeurs de SQL. Lorsque la sous-requête inclut des valeurs NULL, l'expression "NOT IN" peut être évaluée comme INCONNU, ce qui entraîne le filtrage de toutes les lignes.

Alternatives

Pour remédier à ce problème problème, utilisez "LEFT JOIN" ou "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)

Ces solutions alternatives gèrent correctement les valeurs NULL et fournissent le résultat souhaité.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!