NOT IN , left outer join 丢失数据！！

[baola]

1.SELECT count(c.org_nummer) FROM customers c
LEFT OUTER JOIN company b ON c.org_nummer=b.org_nummer
WHERE (c.orgtype='A1' OR c.orgtype='A2' OR
c.orgtype='A0')
AND c.AVSLUTT < 1 得到总和结果为：302 177

2.SELECT count(c.org_nummer) FROM customers c
LEFT OUTER JOIN company b ON c.org_nummer=b.org_nummer
WHERE (c.orgtype='A1' OR c.orgtype='A2' OR
c.orgtype='A0') AND bc.bransjeID IN
(75121,75122,55523,80423,85321,80100,80210,80220,80302,80303,80309,80301
,80304,1125,80426,1122,92721,2014,80410,60220,50500,52260,51310,52112,85
313,85140,85120,80423,85321,80210,80220) AND c.AVSLUTT < 1 .
结果为：10 993
3.SELECT count(c.org_nummer) FROM customers c
LEFT OUTER JOIN company b ON c.org_nummer=b.org_nummer
WHERE (c.orgtype='A1' OR c.orgtype='A2' OR
c.orgtype='A0') AND bc.bransjeID NOT IN
(75121,75122,55523,80423,85321,80100,80210,80220,80302,80303,80309,80301
,80304,1125,80426,1122,92721,2014,80410,60220,50500,52260,51310,52112,85
313,85140,85120,80423,85321,80210,80220) AND c.AVSLUTT < 1 .
结果为：203 784 。
按照not in 和in 的理解，应该第2句和第3句的和等于等一个，但是 2+3=214 784 。丢出了数据，大家帮着分析一下，问题出在了哪里？

[capcom]

那可不一定哦！

你的表里面有NULL值啊！

[pbsql]

你的理解错误

[zjcxc]

应该是NULL值的影响,你检查一下你的表中,在on/in/where涉及的字段中是否有NULL值.

[zheninchangjiang]

用set ansi_nulls on/off来设置一下再看看是否会有不同

[realgz]

同意1、2、3、4、5、6、7。。。。

[pbsql]

不一定是NULL

[capcom]

举个例子：
declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @a values(3,3)
insert @a values(4,4)
insert @b values(1,1)
insert @b values(3,3)
insert @b values(5,5)
insert @b values(7,7)

select count(a.a) from @a a left join @b b on a.a=b.a
select * from @a a left join @b b on a.a=b.a
select count(a.a) from @a a left join @b b on a.a=b.a where b.b in (1,3)
select count(a.a) from @a a left join @b b on a.a=b.a where b.b not in (1,3)

[baola]

Ok.谢谢！是因为有null的问题！！