A表:
id,nameA
1,11a
2,22a
3,33a
B表:
id,nameB
1, 11b
2, 22b
4, 44b
要想得到查询结果
id,nameA,nameB
1, 11a, 11b
2, 22a, 22b
3, 33a, 0
4, 0, 44b
一句sql可以实现吗?
谢谢
id,nameA
1,11a
2,22a
3,33a
B表:
id,nameB
1, 11b
2, 22b
4, 44b
要想得到查询结果
id,nameA,nameB
1, 11a, 11b
2, 22a, 22b
3, 33a, 0
4, 0, 44b
一句sql可以实现吗?
谢谢
nvl(a.id,b.id) as id,
nvl(nameA,0) as nameA,
nvl(nameB,0) as nameB
from
a
full outer join
b
on
a.id=b.id
------------------------------------------------------------------------
select
o.id,
nvl(p.nameA,0) as nameA,
nvl(q.nameB,0) as nameB
from
(select distinct id from A union select distinct id from B) o,
A p,
B q
where
o.id=p.id(+)
and
o.id=q.id(+)
但我的A表,B表本身也是很复杂的查询。和下面的方法相比,哪种更好?select
nvl(id1,id2) id,
a1,
a2
from
(
select a.id id1,a.nameA,b.id id2,b.nameB from a,b where a.id=b.id(+)
union
select a.id id1,a.nameA,b.id id2,b.nameB from a,b where a.id(+)=b.id
)