select * from #t1 a where not exists(select 1 from #t2 where id_t1=a.id)
把not去掉就可以了。 --或者:用嵌套子查询select * from t1 where id in(select id_t1 from t2)
楼主的需求描述得有问题,如果是查找#t2.id_t1与#t1.id有关联的#t1表的数据,可以用以下语句试试 if object_id('tempdb.dbo.#t1') is not null drop table #t1 go create table #t1([id] int,[name] varchar(1)) insert #t1 select 1,'a' union all select 2,'b' union all select 3,'c' go if object_id('tempdb.dbo.#t2') is not null drop table #t2 go create table #t2([id] int,[id_t1] int,[f1] varchar(2)) insert #t2 select 1,1,'a1' union all select 2,1,'a2' union all select 3,3,'c1' goselect * from #t1 a where exists(select 1 from #t2 where id_t1=a.id)/** id name ----------- ---- 1 a 3 c(2 行受影响) **/
select #t1.name,id_t1,f1 from #t2 left join #t1 on #t1.id=#t2.id
把not去掉就可以了。
--或者:用嵌套子查询select * from t1 where id in(select id_t1 from t2)
if object_id('tempdb.dbo.#t1') is not null drop table #t1
go
create table #t1([id] int,[name] varchar(1))
insert #t1
select 1,'a' union all
select 2,'b' union all
select 3,'c'
go
if object_id('tempdb.dbo.#t2') is not null drop table #t2
go
create table #t2([id] int,[id_t1] int,[f1] varchar(2))
insert #t2
select 1,1,'a1' union all
select 2,1,'a2' union all
select 3,3,'c1'
goselect * from #t1 a where exists(select 1 from #t2 where id_t1=a.id)/**
id name
----------- ----
1 a
3 c(2 行受影响)
**/