Mengapa MySQL 'TIDAK MASUK' Gagal dengan NULL Merentas Berbilang Jadual, dan Bagaimana Saya Boleh Membetulkannya?

MySQL "NOT IN" Query Merentas Tiga Jadual: Perangkap dan Penyelesaian

Dalam MySQL, operator "NOT IN" biasanya digunakan untuk mendapatkan semula rekod yang tidak wujud dalam jadual tertentu. Walau bagaimanapun, apabila bekerja dengan berbilang jadual, adalah penting untuk mengetahui potensi perangkapnya.

Satu senario di mana "TIDAK MASUK" boleh membawa kepada hasil yang tidak dijangka ialah apabila jadual yang dicari mungkin mengandungi nilai NULL. Isu ini ditemui dalam pertanyaan yang membandingkan data daripada jadual "gred" dan "penilaian". Pertanyaan ini bertujuan untuk mengenal pasti rekod dalam "gred" yang tidak sepadan dengan mana-mana entri dalam "penilaian", tetapi ia gagal mengembalikan sebarang hasil jika nama "JOHN" tidak terdapat dalam kedua-dua jadual.

Untuk mengelakkan ini isu, adalah disyorkan untuk menggunakan kaedah alternatif seperti NOT EXISTS atau left joins dan bukannya "NOT IN." Pendekatan ini tidak mengalami potensi nilai NULL untuk mengganggu pertanyaan.

Selain itu, adalah dinasihatkan untuk menggunakan cantuman eksplisit dan bukannya sintaks warisan yang menggunakan klausa WHERE untuk menyertai jadual. Gabungan eksplisit memberikan kejelasan yang lebih jelas dan mengelakkan masalah prestasi yang berpotensi.

Untuk menggambarkan lagi kelemahan "TIDAK MASUK" apabila berurusan dengan nilai NULL, pertimbangkan contoh berikut:

Struktur Jadual:

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

Ketulan 1:

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

Hasil Jangkaan: 3 baris (seperti yang dijangkakan)

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

Keputusan Tidak Dijangka: Tiada baris dikembalikan

Tingkah laku yang tidak dijangka ini timbul disebabkan oleh logik tiga nilai yang digunakan oleh MySQL. Apabila nilai NULL terlibat, pertanyaan diterjemahkan kepada:

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

yang bersamaan dengan:

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

Oleh kerana ungkapan "status=NULL" dinilai kepada UNKNOWN, keseluruhan ungkapan menjadi TIDAK DIKETAHUI, dan semua baris ditapis keluar.

Penyelesaian:

Untuk mengelakkan isu ini, gunakan pendekatan alternatif seperti gabungan kiri atau TIDAK WUJUD:

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

atau

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

Atas ialah kandungan terperinci Mengapa MySQL 'TIDAK MASUK' Gagal dengan NULL Merentas Berbilang Jadual, dan Bagaimana Saya Boleh Membetulkannya?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!