select a.id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2 from a full join b on (a.id=b.id)SQL> with a as (select 1 id,'' id1,3 id2 from dual 2 union 3 select 2 id,'' id1,4 id2 from dual 4 ), 5 b as (select 1 id,3 id1,'' id2 from dual 6 union 7 select 3 id,4 id1,'' id2 from dual 8 ) 9 select nvl(a.id,b.id) id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2 10 from a full join b 11 on (a.id=b.id) 12 /
--full 跟union SQL> edi 已写入 file afiedt.buf 1 select nvl(a.id1,b.id1),nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3 2* from a full join b on(a.id1=b.id1) SQL> /NVL(A.ID1,B.ID1) ID2 ID3 ---------------- ---------- ---------- 1 3 3 2 4 3 4SQL> select a.id1,nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3 2 from a left join b on(a.id1=b.id1) 3 union 4 select b.id1,nvl(b.id2,a.id2) id2,nvl(b.id3,a.id3) id3 5 from b left join a on(a.id1=b.id1) 6 / ID1 ID2 ID3 ---------- ---------- ---------- 1 3 3 2 4 3 4
select nvl(a.id1,b.id1),nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3 from a full join b on(a.id1=b.id1)
select a.col1,b.col2,a.col3 from a,b where a.col1=b.col1(+) union select b.col1,b.col2,a.col3 from a,b where a.col1(+)=b.col1
with a as (select 1 id,'' id1,3 id2 from dual union select 2 id,'' id1,4 id2 from dual ), b as (select 1 id,3 id1,'' id2 from dual union select 3 id,4 id1,'' id2 from dual ) select id,max(id1),max(id2) from ( select id,id1,id2 from a union select id,id1,id2 from b ) group by id
with a as ( select 1 id,'' id1,3 id2 from dual union select 2 id,'' id1,4 id2 from dual ), b as (select 1 id,3 id1,'' id2 from dual union select 3 id,4 id1,'' id2 from dual )
select a.id as id, nvl(a.id1, b.id1) as id1, nvl(a.id2, b.id2) as id2 from a full join b on (a.id = b.id)还有啊, 你有没有这种可能啊,就是A B表中同一记录,ID1值或者ID2值都不为空, 哪你想取谁的为准呢 ? 表A 1,null, 3 2,null,4 3,7,4 表B 1,3,null 3,4,null
from a full join b
on (a.id=b.id)SQL> with a as (select 1 id,'' id1,3 id2 from dual
2 union
3 select 2 id,'' id1,4 id2 from dual
4 ),
5 b as (select 1 id,3 id1,'' id2 from dual
6 union
7 select 3 id,4 id1,'' id2 from dual
8 )
9 select nvl(a.id,b.id) id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2
10 from a full join b
11 on (a.id=b.id)
12 /
ID ID1 ID2
---------- ---------------------------------------- ----------
1 3 3
2 4
3 4
SQL>
--full 跟union
SQL> edi
已写入 file afiedt.buf 1 select nvl(a.id1,b.id1),nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3
2* from a full join b on(a.id1=b.id1)
SQL> /NVL(A.ID1,B.ID1) ID2 ID3
---------------- ---------- ----------
1 3 3
2 4
3 4SQL> select a.id1,nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3
2 from a left join b on(a.id1=b.id1)
3 union
4 select b.id1,nvl(b.id2,a.id2) id2,nvl(b.id3,a.id3) id3
5 from b left join a on(a.id1=b.id1)
6 / ID1 ID2 ID3
---------- ---------- ----------
1 3 3
2 4
3 4
from a full join b on(a.id1=b.id1)
select a.col1,b.col2,a.col3 from a,b where a.col1=b.col1(+)
union
select b.col1,b.col2,a.col3 from a,b where a.col1(+)=b.col1
union
select 2 id,'' id1,4 id2 from dual
),
b as (select 1 id,3 id1,'' id2 from dual
union
select 3 id,4 id1,'' id2 from dual
)
select id,max(id1),max(id2)
from (
select id,id1,id2 from a
union
select id,id1,id2 from b
)
group by id
with a as
(
select 1 id,'' id1,3 id2 from dual
union
select 2 id,'' id1,4 id2 from dual
),
b as (select 1 id,3 id1,'' id2 from dual
union
select 3 id,4 id1,'' id2 from dual
)
select a.id as id, nvl(a.id1, b.id1) as id1, nvl(a.id2, b.id2) as id2
from a
full join b on (a.id = b.id)还有啊, 你有没有这种可能啊,就是A B表中同一记录,ID1值或者ID2值都不为空, 哪你想取谁的为准呢 ?
表A
1,null, 3
2,null,4
3,7,4 表B 1,3,null
3,4,null