Avoid NOT IN
像瘟疫一样如果
SELECT ID_Courses FROM Evaluation where `NAME`='JOHN' and Year=1
可能包含 NULL。相反,使用 NOT EXISTS 或左连接
使用显式连接,而不是使用 1980 年代风格的连接WHERE
clause
为了说明 NOT IN 的痛苦:
SQL NOT IN() 危险
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);
** 3 行,如预期 **
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);
没有行吧?
显然这是“不正确的”。它源于 SQL 对三值逻辑的使用,
由 NULL 的存在驱动,NULL 是一个表示缺失(或未知)信息的非值。
对于 NOT IN, Chunk2,它的翻译如下:
status NOT IN ('married', 'divorced', 'widowed', NULL)
这相当于:
NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)
表达式“status=NULL”的计算结果为 UNKNOWN,并且根据三值逻辑规则,
NOT UNKNOWN 也评估为 UNKNOWN。结果,所有行都被过滤掉,并且查询返回一个空集。
可能的解决方案包括:
select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null
or use not exists