select distinct a.*
from t_function_action a
left join t_menu_action b on a.fid=b.action_fid
left join t_role_menu_action c on b.fid=c.menu_action_fid
left join t_function_menu d on b.menu_fid=d.fid
left join t_user_role e on c.role_fid= e.role_fid and e.role_fid=c.role_fid
where e.user_fid=1 and d.paren_fid=76;
select x.*
from t_function_action x
where x.fid in
(select c.action_fid
from t_menu_action c
where c.fid in
(select b.menu_action_fid
from t_role_menu_action b
where b.role_fid in (select a.role_fid
from t_user_role a
where a.user_fid = 1))
and c.menu_fid in
(select fid from t_function_menu t where t.paren_fid = 76));
from t_function_action a
left join t_menu_action b on a.fid=b.action_fid
left join t_role_menu_action c on b.fid=c.menu_action_fid
left join t_function_menu d on b.menu_fid=d.fid
left join t_user_role e on c.role_fid= e.role_fid and e.role_fid=c.role_fid
where e.user_fid=1 and d.paren_fid=76;
select x.*
from t_function_action x
where x.fid in
(select c.action_fid
from t_menu_action c
where c.fid in
(select b.menu_action_fid
from t_role_menu_action b
where b.role_fid in (select a.role_fid
from t_user_role a
where a.user_fid = 1))
and c.menu_fid in
(select fid from t_function_menu t where t.paren_fid = 76));
2、具体来说,你需要分别查看两语句的执行计划。根据执行计划来做决定。关于SQL优化的东西不是只贴出SQL就能看出使用谁好。
oracle现在优化规则一般都是基于COST的。
所以还涉及到统计数据的收集、其会根据表上的索引、数据量各种统计指标值来决定使用什么计划。
/*
第一种方法的效率相对要高些,使用到了左连接,这里不是说左连接的效率高,是与第二种方法进行比较
第二种方法,它使用了嵌套查询,和in操作符,
这里特别注意in操作符,在实际应用中,我们应该多使用exists而不是in
*/