一个查询优化的问题

[redshiliu]

现在有3各表或者视图 a,b,c,d都有一个字段code，现要查找a中code同时在b,c,d中的记录
是用多个in子查询号还是先讲b,c,d inner join了好？如下：

方案1:
select code from a where
code in (select code from b) and
code in (select code from c) and
code in (select code from d)

方案2:
select code from a where code in (
select b.code from b,c,d where b.code=c.code and b.code=d.code)

方案3:
select code from a where code in (
select t1.code from (select b.code from b) t1
inner join (select c.code from c) t2 on t1.code=t2.code
inner join (select d.code from d) t3 on t1.code=t3.code
)

请教下三个方案的优缺点及执行流程。

[tg_hgh163]

[Quote=引用 2 楼 acherat 的回复:]
SQL code


select distinct a.code from a,b,c,d where a.code=b.code and b.code=c.code and b.code=d.code
[/Quote]


这个最直接了

[redshiliu]

在表比较多的时候（大于3个）是用inner join还是in子查询好？

[fanzhouqi]

看不出来，要看执行计划才知道

[AcHerat]

select a.code 
from a join (select b.code from b,c,d where b.code=c.code and b.code=d.code) t 
       on a.code = t.code

[AcHerat]

select distinct a.code from a,b,c,d where a.code=b.code and b.code=c.code and b.code=d.code

[唐诗三百首]

select a.*
from a
inner join b on a.code=b.code
inner join c on a.code=c.code
inner join d on a.code=d.code

[大力水手]

[Quote=引用 3 楼 ap0405140 的回复:]

SQL code

select a.*
from a
inner join b on a.code=b.code
inner join c on a.code=c.code
inner join d on a.code=d.code
[/Quote]
不写了直接引用