Why Does MySQL's 'NOT IN' Fail with Three Tables and NULLs, and What Are the Better Alternatives?

MySQL “NOT IN” Querying Three Tables

When working with three tables, using the "NOT IN" operator in a query can be problematic, especially when dealing with NULL values. This article demonstrates the risks associated with using "NOT IN" and suggests alternative solutions.

The Problem

The provided query seeks to compare two tables, Grade and Evaluation, identifying records in Grade that are not present in Evaluation. However, when the specified name ("JOHN") does not exist in Evaluation, the query fails to return any output.

Solution

To address this issue, avoid using "NOT IN" if the subquery used to filter data may contain NULL values. Instead, consider using "NOT EXISTS" or left joins.

Let's illustrate the potential dangers of using "NOT IN":

SQL "NOT IN" Danger

Create the mStatus and people tables with sample data:

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

Expected Output: 3 rows

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

Unexpected Output: 0 rows

The second chunk unexpectedly returns no rows because of SQL's three-valued logic. When the subquery includes NULL values, the "NOT IN" expression may evaluate to UNKNOWN, resulting in all rows being filtered out.

Alternatives

To remedy this issue, use "LEFT JOIN" or "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)

These alternative solutions correctly handle NULL values and provide the desired output.

The above is the detailed content of Why Does MySQL's 'NOT IN' Fail with Three Tables and NULLs, and What Are the Better Alternatives?. For more information, please follow other related articles on the PHP Chinese website!