select * from b where b.caontract not in (select contract from a )
select * from b where not exists (select * from a where b.caontract=a.caontract)
not in not exists left join .... on a.contract=b.contract and b.contract is null 都可以.
用select * from b where b.caontract not in (select contract from a )找出了几万条记录,但都不对,我总共才几百条记录,它把a的每一行和b的所有的比较,所以一行一比较就有几白条,几百行比较就会上万用select * from b where not exists (select * from a where b.contract=a.contract)出了40条记录,里面有些是空的合同号,不过凭眼睛看,能找到不是空的合同号,符合我的要求
select contract from b where b.caontract not in (select contract from a )
推荐去看一下《sql cookbook》,上边会告诉你not in 和 not exists的区别 用select * from b where b.caontract not in (select contract from a)时候需要注意,当子查询中的结果存在null时,比如你的结果为(‘1’‘2’,null),那样的话结果就不对了,用not exists就不存在这个问题了
from b
where not exists
(select *
from a
where b.caontract=a.caontract)
not exists
left join .... on a.contract=b.contract and b.contract is null
都可以.
用select * from b where b.caontract not in (select contract from a)时候需要注意,当子查询中的结果存在null时,比如你的结果为(‘1’‘2’,null),那样的话结果就不对了,用not exists就不存在这个问题了