Why Does MySQL's 'NOT IN' Fail with NULLs Across Multiple Tables, and How Can I Fix It?

MySQL "NOT IN" Query Across Three Tables: Pitfalls and Solutions

In MySQL, the "NOT IN" operator is commonly used to retrieve records that do not exist in a specified table. However, when working with multiple tables, it's important to be aware of its potential pitfalls.

One scenario where "NOT IN" can lead to unexpected results is when the table being searched for may contain NULL values. This issue was encountered in a query comparing data from "grade" and "evaluation" tables. The query aimed to identify records in "grade" that did not match any entries in "evaluation," but it failed to return any results if the name "JOHN" was not present in both tables.

To avoid this issue, it is recommended to use alternative methods such as NOT EXISTS or left joins instead of "NOT IN." These approaches do not suffer from the potential for NULL values to disrupt the query.

In addition, it is advisable to use explicit joins rather than the legacy syntax that uses the WHERE clause to join tables. Explicit joins provide greater clarity and avoid potential performance issues.

To further illustrate the drawbacks of "NOT IN" when dealing with NULL values, consider the following example:

Table Structure:

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

Expected Result: 3 rows (as expected)

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

Unexpected Result: No rows returned

This unexpected behavior arises due to the three-valued logic used by MySQL. When NULL values are involved, the query translates to:

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

which is equivalent to:

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

Since the expression "status=NULL" evaluates to UNKNOWN, the entire expression becomes UNKNOWN, and all rows are filtered out.

Solution:

To avoid this issue, use alternative approaches such as left joins or NOT EXISTS:

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

or

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

The above is the detailed content of Why Does MySQL's 'NOT IN' Fail with NULLs Across Multiple Tables, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!