方法1:嵌套子查询
select col1,col2
from tab1
where id in (select 2_id
from tab2
where order_num in ( select order_num
from tab3
where prod_id='AAA'))
方法2:无嵌套子查询
select col1,col2
from tab1,tab2,tab3
where
tab1.id=tab2.id
and tab2.order_num =tab3.order_num
and tab3.prod_id='AAA'
方法3:INNER JOIN
select col1,col2
from tab1
inner join tab2 on tab1.id=tab2.id
inner join tab3 on tab2.order_num =tab3.order_num
where
prod_id='AAA'
我估计不同的数据库的优化器,性能应该是有千差万别的。
请各位在不同的数据库上进行测试,并帮忙反馈一下测试结果哟