T-SQL筆記31_當NOT IN遇到null欄位造成的資料筆數錯誤

 



記得將null欄位都先替換為空字串再丟給not in 做匹配
才不會誤判!!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE TABLE student (
	A1 nvarchar(50),
	A2 nvarchar(50),
	A3 nvarchar(50),
);
insert into student (A1,A2,A3) values ('二班','林芳',null);
insert into student (A1,A2,A3) values ('二班','林蘭','丰采');
insert into student (A1,A2,A3) values ('二班','張清',null);
insert into student (A1,A2,A3) values ('二班','林芬',null);
insert into student (A1,A2,A3) values ('二班','吳玉','班長');
insert into student (A1,A2,A3) values ('二班','曾儀','書記');

select * from student WHERE A3 NOT IN ('丰采') -- bad
select * from student WHERE ISNULL(A3,'') NOT IN ('丰采') --good





























留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題