select * from t1 where not exists(select 1 from t2 where t1.xxx = t2.xxx)
select *from 表1 where not exists(select 1 from 表2 where 表2.XXX=表1.XXX) select *from 表1 where XXX not in(select XXX from 表2 )
select * from 表1 where not exists(select * from 表2 where xxx=表1.xxx)
--表1的XXX中存在,而表2的XXX中不存在的值SELECT * FROM [表1] WHERE XXX NOT IN (SELECT XXX FROM [表2])SELECT * FROM [表1] WHERE NOT EXISTS(SELECT 1 FROM [表2] WHERE [表1].XXX = [表2].XXX)
if object_id('tb1') > 0 begin drop table tb1 endif object_id('tb2') > 0 begin drop table tb2 endcreate table tb1(xxx int) insert into tb1 select 1 union all select 2 union all select 3 union all select 4 union all select 5create table tb2(xxx int) insert into tb2 select 1 union all select 3 union all select 5 union all select 7 union all select 9select * from tb1 where tb1.xxx not in (select tb2.xxx from tb2)select * from tb1 where not exists (select 1 from tb2 where tb1.xxx = tb2.xxx) 结果 xxx 2 4
用右连接. select A.* from A left join B on A.XXX=B.XXX where B.XXX is null 或者 select * from A where A.XXX not in(select XXX from B)
select A.* from A left jion B on a.xxx=b.xxx where b.xxx is null
select *from 表1 where XXX not in(select XXX from 表2 )
if object_id('tb1') > 0
begin
drop table tb1
endif object_id('tb2') > 0
begin
drop table tb2
endcreate table tb1(xxx int)
insert into tb1 select 1
union all select 2
union all select 3
union all select 4
union all select 5create table tb2(xxx int)
insert into tb2 select 1
union all select 3
union all select 5
union all select 7
union all select 9select * from tb1 where tb1.xxx not in (select tb2.xxx from tb2)select * from tb1 where not exists (select 1 from tb2 where tb1.xxx = tb2.xxx)
结果
xxx
2
4
select A.* from A left join B on A.XXX=B.XXX where B.XXX is null
或者
select * from A where A.XXX not in(select XXX from B)