--> 测试数据: @表1 declare @表1 table (id int,a int) insert into @表1 select 1,33 union all select 2,33 union all select 3,43--> 测试数据: @表2 declare @表2 table (id int,b int,iid int) insert into @表2 select 1,2,1 union all select 2,2,1 union all select 3,3,2 union all select 4,3,2select a.* from @表1 a LEFT JOIN @表2 b ON a.id=b.iid WHERE b.iid IS NULL /* id a ----------- ----------- 3 43 */
--一条语句就是这样的 select a.* from 表1 a left join 表2 b on a.id=b.iid where b.iid is null
select a.* from 表1 a where exists(select 1 from 表2 b where a.id=b.iid)
select a.* from tbtable1 a where not exists(select 1 from tbtable2 b where a.id=b.iid)
--> 测试数据: @表1
declare @表1 table (id int,a int)
insert into @表1
select 1,33 union all
select 2,33 union all
select 3,43--> 测试数据: @表2
declare @表2 table (id int,b int,iid int)
insert into @表2
select 1,2,1 union all
select 2,2,1 union all
select 3,3,2 union all
select 4,3,2select a.* from @表1 a LEFT JOIN @表2 b
ON a.id=b.iid WHERE b.iid IS NULL
/*
id a
----------- -----------
3 43
*/
select a.* from 表1 a left join 表2 b on a.id=b.iid where b.iid is null
select a.* from tbtable1 a where not exists(select 1 from tbtable2 b where a.id=b.iid)