两张表关联查询问题

[puckgod]

有表A（id，col1，col2，col3），B（id，col1，col2，col3，col4），其中前4个字段是相同的，两个表通过id字段关联，现在需要关联查询符合条件的相关数据。
条件： 如果B表中的id字段值在A表中存在，那么取B中数据，否则取A中数据，结果只要前4个字段，需要用一条sql实现。

[y112102]

select a.id,
rownum (select a.id
from a
inner join b on (a.id = b.id)
union
select a.id
from a
where not EXISTS
(select 1 from a, b where a.id = b.id))
where rownum between 1 and 4
group by a.id

[y112102]

不好意思 少打了关键字
select a.id, rownum
from (select a.id
from a
inner join b on (a.id = b.id)
union
select a.id
from a
where not EXISTS (select 1 from a, b where a.id = b.id))
where rownum between 1 and 4
group by a.id

[puckgod]

木有人？

[yixilan]

你执行试试。

select b.id, b.col1, b.col2, b.col3 from a, b
where a.id = b.id
union
select a.id, a.col1, a.col2, a.col3 from a
where not EXISTS (select 1 from a, b where a.id = b.id)

[zujinsheng]

select A.* from A
union
select B.id,B.col1,B.col2,B.col3 from A,B
where B.id not in (select A.id from A)

[zujinsheng]

靠..理解反意思了..

select B.id,B.col1,B.col2,B.col3 from B
union
select A.* from A,B
 where A.id not in (select B.id from B)