熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中的外連接簡單介紹

2013-11-13 16:17:40  來源: Oracle 

  在講外連接之前先舉例介紹內連接也就是一般的相等連接

  select * from a b where aid = bid;

  對於外連接Oracle中可以使用(+)來表示i可以使用LEFT/RIGHT/FULL OUTER JOIN下面將配合實例一一介紹

   LEFT OUTER JOIN左外關聯

  SELECT elast_name edepartment_id ddepartment_name

  FROM employees e

  LEFT OUTER JOIN departments d

  ON (edepartment_id = ddepartment_id);

  等價於

  SELECT elast_name edepartment_id ddepartment_name

  FROM employees e departments d

  WHERE edepartment_id=ddepartment_id(+);

  結果為所有員工及對應部門的記錄包括沒有對應部門編號department_id的員工記錄

   RIGHT OUTER JOIN右外關聯

  SELECT elast_name edepartment_id ddepartment_name

  FROM employees e

  RIGHT OUTER JOIN departments d

  ON (edepartment_id = ddepartment_id);

  等價於

  SELECT elast_name edepartment_id ddepartment_name

  FROM employees e departments d

  WHERE edepartment_id(+)=ddepartment_id;

  結果為所有員工及對應部門的記錄包括沒有任何員工的部門記錄

   FULL OUTER JOIN全外關聯

  SELECT elast_name edepartment_id ddepartment_name

  FROM employees e

  FULL OUTER JOIN departments d

  ON (edepartment_id = ddepartment_id);

  結果為所有員工及對應部門的記錄包括沒有對應部門編號department_id的員工記錄和沒有任何員工的部門記錄


From:http://tw.wingwit.com/Article/program/Oracle/201311/18099.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.