Pourquoi 'NOT IN' de MySQL échoue-t-il avec des valeurs NULL sur plusieurs tables et comment puis-je y remédier ?

Requête MySQL "NOT IN" sur trois tables : pièges et solutions

Dans MySQL, l'opérateur "NOT IN" est couramment utilisé pour récupérer les enregistrements qui n'existent pas dans une table spécifiée. Cependant, lorsque vous travaillez avec plusieurs tables, il est important d'être conscient de ses pièges potentiels.

Un scénario dans lequel « NOT IN » peut conduire à des résultats inattendus est celui où la table recherchée peut contenir des valeurs NULL. Ce problème a été rencontré lors d’une requête comparant les données des tables « note » et « évaluation ». La requête visait à identifier les enregistrements dans « grade » qui ne correspondaient à aucune entrée dans « évaluation », mais elle n'a renvoyé aucun résultat si le nom « JOHN » n'était pas présent dans les deux tables.

Pour éviter cela problème, il est recommandé d'utiliser des méthodes alternatives telles que NOT EXISTS ou left joins au lieu de "NOT IN". Ces approches ne souffrent pas du risque que les valeurs NULL perturbent la requête.

De plus, il est conseillé d'utiliser des jointures explicites plutôt que la syntaxe héritée qui utilise la clause WHERE pour joindre des tables. Les jointures explicites offrent une plus grande clarté et évitent les problèmes de performances potentiels.

Pour illustrer davantage les inconvénients de « NOT IN » lorsqu'il s'agit de valeurs NULL, considérons l'exemple suivant :

Structure de table :

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

Morceau 1 :

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

Résultat attendu : 3 lignes (comme prévu)

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

Résultat inattendu : Aucune ligne returnné

Ce comportement inattendu est dû à la logique à trois valeurs utilisée par MySQL. Lorsque des valeurs NULL sont impliquées, la requête se traduit par :

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

ce qui équivaut à :

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

Puisque l'expression "status=NULL" est évaluée à INCONNU, l'expression entière devient INCONNU, et toutes les lignes sont filtrées out.

Solution :

Pour éviter ce problème, utilisez des approches alternatives telles que les jointures à gauche ou NOT EXISTS :

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

ou

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

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!