Oracle SQL99 外串連的寫法區別

1: cartesian 笛卡爾積
返回的結果集合：  
一般按照From後表的順序，先從t1種抽取所有的記錄和t2的第一條記錄結合
                然後從t1種抽取所有的記錄和t2的第二條記錄結合
                然後從t1種抽取所有的記錄和t2的第...條記錄結合
SQL99：
SELECT t1.department_id, t2.employee_id
  FROM t_department t1 cross joint_employee t2;  
關鍵字：  cross join

 

oracle:
SELECT t1.department_id, t2.employee_id
  FROM t_department t1,t_employee t2;

2:Jion(inner join)
2.1 EquiJoin :等值串連
Sql99:
SELECT * FROM t_department;
DEPARTMENT_ID LOCATION_ID DEPARTMENT_NAME
------------- -------------------------------------------------------------------------------------------
D_1         L_1       D_N
D_2         L_2       D_N2
SQL> SELECT * FROM t_employee;

EMPLOYEE_ID DEPARTMENT_ID EMPLOYEE_NAME
----------- ---------------------------------------------------------------------------------------------
E_3       D_4        E_N4
E_1       D_1        E_N1
E_2       D_3        E_N2
SQL> SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1
  3         JOINt_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_1

Oracle：
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1, t_employee t2
  3  WHERE t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_1

2.2：non_equiJoin:非等值串連
也是做笛卡爾積,找出符合串連條件的
SQL99：
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1
  3         JOINt_employee t2 ON t1.department_id <>t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_3
D_2         E_3
D_2         E_1
D_1         E_2
D_2         E_2

Oracle:
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1, t_employee t2
  3  WHERE t1.department_id<> t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_3
D_2         E_3
D_2         E_1
D_1         E_2
D_2         E_2

2.3 outer join

2.3.1 左外關聯
員工表增加記錄
EMPLOYEE_ID DEPARTMENT_ID EMPLOYEE_NAME
       E_4           D_1               E_N8

找出部門的所有員工，如果部門沒有員工，則員工資訊為NULL
SQL99：
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1
  3         LEFT OUTERJOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_4
D_1         E_1
D_2         null(不會顯示)

Oracle: 等號在(+)的左邊
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1, t_employee t2
  3  WHERE t1.department_id =t2.department_id(+);

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_4
D_1         E_1
D_2         null(不會顯示)      

2.3.2 右外關聯
找出全體員工的部門資訊,如果員工部門不存在要求顯示員工的資訊,部門則為NULL
SQL99：
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1
  3  RIGHT OUTER JOIN t_employee t2 ONt1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_1
D_1         E_4
           E_2
           E_3
Oracle:
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1, t_employee t2
  3  WHERE t1.department_id(+) =t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_4
D_1         E_1
           E_2
           E_3

2.3.3 全外關聯
找出所有員工和所有部門的部門員工資訊,如果對應的資訊不存在則以NULL顯示.
SQL 99:
SELECT t1.department_id, t2.employee_id
  2         FROMt_department t1
  3         FULL OUTERJOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_4
D_1         E_1
D_2         
           E_2
           E_3

Oracle:
  不存在

3 其它情況
3.1. 同名欄位Join
同名欄位自動等值Join
SELECT  department_id,t2.employee_id -- 同名欄位不能寫成 “表名.欄位名”
       FROM t_department t1 NATURAL
       JOIN t_employee t2;
DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_1

分別在t_department，t_employee增加“TEST”欄位，然後在t_department中的值賦為“2”，在t_employee中賦“1”
SELECT  department_id,  test,t2.employee_id
  2         FROMt_department t1 NATURAL
  3         JOINt_employee t2;

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
沒有結果集合

3.2 多個同名欄位時，指定同名欄位Join
SELECT department_id, t1.test, t2.employee_id
  2         FROMt_department t1
  3         JOINt_employee t2
  4  USING (department_id);

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
D_1         2   E_4
D_1         2   E_1

SELECT t1.department_id, test, t2.employee_id
  2         FROMt_department t1
  3         JOINt_employee t2
  4  USING (test);

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
沒有結果集合

4. seft join(從詞的意思可以看出是自己和自己做串連操作)
沒有想出好例子.

5. 增加過濾條件
SQL99
SELECT department_id, t2.employee_id
  2         FROMt_department t1
  3         JOINt_employee t2
  4  USING (department_id)
  5  WHERE t2.employee_id >='E_0';

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1         E_4
D_1         E_1

Oracle：
在後面加上 And 條件即可。

6. 多表Join
求出三個表中有效依賴關係的資料
SELECT t1.department_id, t2.employee_id, t3.location_id
  2         FROMt_department t1
  3         JOINt_employee t2 ON t1.department_id = t2.department_id
  4         JOINt_Location t3 ON t1.location_id = t3.location_id;

DEPARTMENT_ID EMPLOYEE_ID LOCATION_ID
------------- ----------- -----------
D_1         E_4       L_1
D_1         E_1       L_1