轉：oracle串連查詢

SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E LEFT OUTER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID



SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E ,HR.DEPARTMENTS D 
WHERE E.DEPARTMENT_ID= D.DEPARTMENT_ID(+)

-----------------
  
SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E RIGHT OUTER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID


SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E ,HR.DEPARTMENTS D 
WHERE E.DEPARTMENT_ID(+)= D.DEPARTMENT_ID  
-------------------------
  
SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E FULL OUTER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

-- 全串連是左串連和右串連的並集
--內串連是左串連和右串連的交集     
 
SELECT COUNT(0) FROM HR.EMPLOYEES CROSS JOIN HR.DEPARTMENTS  --共2889條記錄，笛卡爾積

SELECT COUNT(0) FROM HR.EMPLOYEES   --107  條記錄
SELECT COUNT(0) FROM HR.DEPARTMENTS   --27 條記錄

SELECT 107 * 27 FROM DUAL;  --2889

-------------------------
SELECT * FROM HR.EMPLOYEES NATURAL JOIN HR.DEPARTMENTS 


SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E INNER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

SELECT E.EMPLOYEE_ID, E.LAST_NAME, DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E INNER JOIN HR.DEPARTMENTS D USING (DEPARTMENT_ID)

講外串連之前，先舉例介紹內串連，也就是一般的相等串連。 

select * from a, b where a.id = b.id; 

對於外串連，oracle中可以使用“(+)”來表示，9i可以使用left/right/full outer join，下面將配合執行個體一一介紹。 

1. left outer join：左外關聯

select e.last_name, e.department_id, d.department_name from employees e left outer join departments d on (e.department_id = d.department_id);

等價於 

select e.last_name, e.department_id, d.department_name from employees e, departments d where e.department_id=d.department_id(+);

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

2. right outer join：右外關聯 

select e.last_name, e.department_id, d.department_name from employees e right outer join departments d on (e.department_id = d.department_id);

等價於 

select e.last_name, e.department_id, d.department_name from employees e, departments d where e.department_id(+)=d.department_id;

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

3. full outer join：全外關聯 

select e.last_name, e.department_id, d.department_name from employees e full outer join departments d on (e.department_id = d.department_id);

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

其實啊 ：外串連就是查兩張表 左串連就是左邊的表全有值，右邊表的值可以為空白（+）

                                                右串連是左邊表值可以為空白（+）    右邊表的值全有

                                                全串連是左串連和右串連的並集 好像是這個符號（U）

                                                內串連是左串連和右串連的交集 。。。  

其餘串連方式：

cross join: 交叉串連，查出的表的笛卡爾積 eg: select * from A cross join B    （A表有M行，B表有N行，結果是M*N行）。

左串連

             a.id=b.id(+) ===> a表內容全部顯示，以左邊的表為基準

              left join : left join 左邊的表全部顯示,以左邊的表為基準；

              select * from emp e left join dept d on e.deptno=d.deptno;

右串連    a.id(+)=b.id ===> b表內容全部顯示，以右表的表為基準。

              right join: right join 右邊的表全部顯示，以右邊的表為基準

             select * from emp e right join dept d on e.deptno=d.deptno;

right join ,left join 沒有 where from 語句等。

   註：+號在=號左邊叫右串連，+號在=右邊叫做左串連。   

自然串連：natural join

           自然串連，natural join 會根據列明，自動建立串連，省略where語句，避免笛卡爾積的出現

   eg: select empno,ename,sal,deptno,loc from emp natural join dept;

using :表示與指定的列相關聯。

eg: select e.ename,e.sal,deptno,d.loc from emp e join dept d using (deptno) where deptno=20;

註：被using 子句所引用的列，在sql 語句中的任何地方不能使用表名或者別名作為首碼。

PS :做外串連的時候，where 條件中不可以加從表的條件，需將從表做個子查詢，做成另外一個表。

eg :select t.acctype, nvl(b.name,t.acctype) as name
      from biacciccardmaptb t, dictcodesettb b
      where b.category = '賬戶類型'
        and t.acctype=b.code(+)
        and t.iccardno = '1000751090001385'   this is wrong

should :

select t.acctype, nvl(b.name,t.acctype) as name
from biacciccardmaptb t, (select * from dictcodesettb where category = '賬戶類型' )b
where 
    t.acctype=b.code(+)
   and t.iccardno = '1000751090001385'

http://bqsongning.blog.163.com/blog/static/115471473200932284753219/