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