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 |
留言
張貼留言