3个表的情况,写起来比较麻烦,执行效率应该还可以
SELECT MAX(a_id), MAX(a_name), MAX(b_id), MAX(b_name), MAX(c_id), MAX(c_name)
FROM(
select M.rowid id,N.a_id, N.a_name,null b_id,null b_name,null c_id,null c_name
from main M,A N WHERE M.a_id(+)=N.a_id
union all
select M.rowid id,null a_id, null a_name,N.b_id,N.b_name,null c_id,null c_name
from main M,B N WHERE M.b_id(+)=N.b_id
union all
select M.rowid id,null a_id, null a_name,null b_id,null b_name,N.c_id,N.c_name
from main M,C N WHERE M.c_id(+)=N.c_id
)group by id
SELECT MAX(a_id), MAX(a_name), MAX(b_id), MAX(b_name), MAX(c_id), MAX(c_name)
FROM(
select M.rowid id,N.a_id, N.a_name,null b_id,null b_name,null c_id,null c_name
from main M,A N WHERE M.a_id(+)=N.a_id
union all
select M.rowid id,null a_id, null a_name,N.b_id,N.b_name,null c_id,null c_name
from main M,B N WHERE M.b_id(+)=N.b_id
union all
select M.rowid id,null a_id, null a_name,null b_id,null b_name,N.c_id,N.c_name
from main M,C N WHERE M.c_id(+)=N.c_id
)group by id
额,谢谢, 但是我外关联的表远远不止, 只要还报ORA-17410 出错
后来通过加VIEW的方法解决了
user_Tab_constraints 主外键约束
(select a.a_name from A where a.a_id = ma.a_id) a.a_name,
(select b.a_name from B where b.a_id = ma.a_id) b.a_name,
....
from
main ma