ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL中的FULL JOIN 問題

SQL中的FULL JOIN 問題

原创 Linux操作系统 作者:jack198409 时间:2008-04-02 13:45:49 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-224919/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    283247