SQL中的FULL JOIN 問題
SQL> with a as (select 'a' name,1 num1 from dual
2 union
3 select 'b' name,2 num1 from dual
4 union
5 select 'c' name,3 num1 from dual
6 ),
7 b as (select 'a' name,1 num2 from dual
8 union
9 select 'd' name,2 num2 from dual
10 union
11 select 'e' name,3 num2 from dual
12 )
13 select nvl(a.name,b.name) name,a.num1,b.num2 from a full join b on a.name=b.name
14 /
NAME NUM1 NUM2
---- ---------- ----------
a 1 1
b 2
c 3
d 2
e 3
二.FULL OUTER JOIN
SQL> with a as (select 'a' name,1 num1 from dual
2 union
3 select 'b' name,2 num1 from dual
4 union
5 select 'c' name,3 num1 from dual
6 ),
7 b as (select 'a' name,1 num2 from dual
8 union
9 select 'd' name,2 num2 from dual
10 union
11 select 'e' name,3 num2 from dual
12 )
13 select nvl(a.name,b.name) name,a.num1,b.num2 from a full outer join b on a.name=b.name
14 /
NAME NUM1 NUM2
---- ---------- ----------
a 1 1
b 2
c 3
d 2
e 3
三.LEFT OUTER JOIN 與RIGHT OUTER JOIN
SQL> with a as (select 'a' name,1 num1 from dual
2 union
3 select 'b' name,2 num1 from dual
4 union
5 select 'c' name,3 num1 from dual
6 ),
7 b as (select 'a' name,1 num2 from dual
8 union
9 select 'd' name,2 num2 from dual
10 union
11 select 'e' name,3 num2 from dual
12 )
13 select a.name name,a.num1 num1,b.num2 num2 from a,b where a.name=b.name(+)
14 union
15 select b.name name,a.num1 num1,b.num2 num2 from a,b where a.name(+)=b.name
SQL> /
NAME NUM1 NUM2
---- ---------- ----------
a 1 1
b 2
c 3
d 2
e 3
總結:ORACLE 中的FULL JOIN 與FULL OUTER JOIN 一樣,同時也等同與兩個表的LEFT JOIN 與RIGHT JOIN 的合并(去處重復行)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10768286/viewspace-224919/ ，如需转载，请注明出处，否则将追究法律责任。
