一个用查询结果再次查询的问题！

[ThankU]

select distinct id
from table1
where name='ZHANG'

比如会返回
id
001
002
004

然后我想用查到的id组再查找table2，返回在table2里存在的id组？(最好不用临时表)
比如
id
001
004

（比如id=002的数据不存在于table2里面）

[csdyyr]

select * from 
(select distinct id
from table1
where name='ZHANG' ) a join table2 as b
on a.id=b.id

[fcuandy]

select * from table2 a where exists(select 1 from table1 where name='ZHANG' and id=a.id)

select a.* from table2 a inner join table1 b on a.id=b.id where b.name='ZHANG'

写法比较多，优先这两种吧。

[AndrewITBOY]

select * 
from table2
  left join (select distinct id 
               from table1 
              where name='ZHANG' 
             ) A on A.id = table2.id
where 1=1

[ljhcy99]

select * from table2
where id not in(
select distinct id
from table1
where name='ZHANG')

[百年树人]

select b.id
from table1 a,table2 b
where a.id=b.id
and a.id='ZHANG'

[让你望见影子的墙]

select a.* from table2 a inner join table1 b on a.id=b.id where b.name='ZHANG'

[mengxj85]

[Quote=引用 2 楼 fcuandy 的回复:]
select * from table2 a where exists(select 1 from table1 where name='ZHANG' and id=a.id)

select a.* from table2 a inner join table1 b on a.id=b.id where b.name='ZHANG'

写法比较多，优先这两种吧。
[/Quote]
顶

[ws_hgo]

select id from table2  where id in(select distinct id 
from table1 
where name='ZHANG' 
)