SQL中的FULL JOIN 問題

SQL> with a as (select 'a' name,1 num1 from dual
H8I/if o%j,_l9t0  2             union
\"T9r$cRGC0  3             select 'b' name,2 num1 from dualITPUB个人空间k|,a:NNVLXj_
  4             unionITPUB个人空间%GP V$Z&e/l;Y
  5             select 'c' name,3 num1 from dualITPUB个人空间&j1|8E{pF
o
  6             ),
\J*B9b&r&l9W0  7       b as (select 'a' name,1 num2 from dualITPUB个人空间QML?7B!?u
  8             union
],ydzeu'j_0  9             select 'd' name,2 num2 from dualITPUB个人空间^"hl[y,n8H6T
 10             unionITPUB个人空间\
gv`d:y
 11             select 'e' name,3 num2 from dualITPUB个人空间}?2^iA\
m
 12             )ITPUB个人空间,HXO/zs`
 13  selectnvl(a.name,b.name)name,a.num1,b.num2 from a full joinb on a.name=b.nameITPUB个人空间n6eu8j]
 14  /ITPUB个人空间p9oOa4}6Q ~p,wFZ
 ITPUB个人空间xRS7Vc)\$zci6~
NAME       NUM1       NUM2ITPUB个人空间wL&Nk]%XM
---- ---------- ----------ITPUB个人空间iqa C:U8~p
a             1          1
?6T,ki/wga9b9S9@g0b             2ITPUB个人空间'\!r4b3q?`Wb
c             3ITPUB个人空间iEAE,sSy
d                        2ITPUB个人空间A&t-_`#D/xh?
e                        3

二.FULL OUTER JOIN

SQL> with a as (select 'a' name,1 num1 from dual
8Z,Ia-UH1x0  2             unionITPUB个人空间)S%xV;tU(w O.`\2h{
  3             select 'b' name,2 num1 from dualITPUB个人空间 @4U| z i/va4|Ki
  4             union
:Uw8rzv-m*X.O1s+Yl0  5             select 'c' name,3 num1 from dualITPUB个人空间fND ~H0_
  6             ),ITPUB个人空间
a|6h)U-y1C{5R3C
  7       b as (select 'a' name,1 num2 from dualITPUB个人空间D
A'CcL}8tM
  8             union
@i#CN|M&OE0  9             select 'd' name,2 num2 from dual
7U$fu/vY'L;SuT4s#{I0 10             unionITPUB个人空间+c"O
D Mx]h
 11             select 'e' name,3 num2 from dualITPUB个人空间ba$q l'CT&K
 12             )ITPUB个人空间1UX-o8` UDF.e
 13  select nvl(a.name,b.name) name,a.num1,b.num2 from afullouterjoinb on a.name=b.name
9w%{PR wY2_0 14  /ITPUB个人空间yd p(sy3l7m?e.L$i
 
}a"[dlX2WlL0NAME       NUM1       NUM2ITPUB个人空间?%v,RF8Tz*y
---- ---------- ----------
/D&b2S,qvJm8`a0a             1          1ITPUB个人空间m5M?2} O-V$E| R
b             2
;d7Ki FA)y0c             3ITPUB个人空间/T
m*OK(Pt
d                        2ITPUB个人空间 c(gXM3p9E+J
e                        3

三.LEFT OUTER JOIN 與RIGHT OUTER JOIN

SQL> with a as (select 'a' name,1 num1 from dual
Kw8l'u mDQ0  2             union
s+U$L5y/_%i;P/s0  3             select 'b' name,2 num1 from dualITPUB个人空间R V}w}Q
  4             unionITPUB个人空间`y,S-x%e9K~6\8PI
  5             select 'c' name,3 num1 from dual
1en;D]Gv rc[&M0  6             ),ITPUB个人空间Sl.Y/x7[7~Bm
  7       b as (select 'a' name,1 num2 from dual
3@/L!z[_0  8             unionITPUB个人空间a ZfoN[i/i ~L
  9             select 'd' name,2 num2 from dualITPUB个人空间)[7i9gr7T
 10             unionITPUB个人空间C9\ J6xG(B$`Q)Ai
 11             select 'e' name,3 num2 from dual
+el*wpGA0 12             )ITPUB个人空间G4G-R|k7T"_r;e0j M
 13  select  a.name name,a.num1 num1,b.num2 num2 from a,b where a.name=b.name(+)
N,h&I]*j2G%v0 14  union
%ZC
P7vSbLMt0 15  select  b.name name,a.num1 num1,b.num2 num2 from a,b where a.name(+)=b.nameITPUB个人空间#T8c'teqt/u+T7nL
SQL> /ITPUB个人空间 O`K5[s Fm8h
 
"L6OHs]B(l0NAME       NUM1       NUM2ITPUB个人空间&wO:B%w)Q
---- ---------- ----------ITPUB个人空间k3T-~#ca}E G~
a             1          1
|-y3C0s:P|&F0b             2
iWL:x0[+B"D0c             3
O9L A0Z;XU9l1N0d                        2ITPUB个人空间 b
j$R"g${F!Hqp
e                        3

總結:ORACLE 中的FULL JOIN 與FULL OUTER JOIN 一樣,同時也等同與兩個表的LEFT JOIN 與RIGHT  JOIN 的合并(去處重復行)