Oracle串連查詢介紹

串連查詢是資料庫查詢語句中使用頻率很高的查詢方式，下面根據Oracle提供的官方文檔學習一下串連查詢：

1. 串連種類：

1）oracle特有串連種類：

   a.Equijoin 等值串連  

SQL> select e.employee_id, e.last_name,
  2                   e.department_id, d.department_id,
  3                   d.location_id
  4  from   employees e, departments d
  5  where  e.department_id = d.department_id;

EMPLOYEE_ID LAST_NAME  DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- -------------        -------------     -----------
        198              OConnell                              50                           50         1500
        199              Grant                                     50                            50         1500
        200              Whalen                                10                            10          1700
        201              Hartstein                              20                            20         1800

......

   b. Non-Equijoin 非等值串連

 

SQL> select e.last_name, e.salary, j.grade
  2  from   employees e, job_grades j
  3  where  e.salary between j.lowest_sal and j.highest_sal;

 

LAST_NAME                     SALARY G
------------------------- ---------- -
Olson                           2100       A
Philtanker                    2200      A
Markle                          2200      A

...

Feeney                          3000 B
Cabrio                          3000 B
Fleaur                          3100 B
Walsh                           3100 B

...

Hartstein                      13000 C
Partners                       13500 C

Partners                       13500 D
Russell                        14000 D
De Haan                        17000 E
Kochhar                        17000 E

   c. Outer join 外串連

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

 

相當於:select e.last_name, e.department_id, d.department_name
             from   employees e RIGHT JOIN departments  d
             ON  e.department_id = d.department_id;

 LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Greenberg                           100 Finance
Sciarra                             100 Finance
Urman                               100 Finance
Popp                                100 Finance
Faviet                              100 Finance
Gietz                               110 Accounting
Higgins                             110 Accounting
                                        Treasury
                                        Corporate Tax
                                        Control And Credit
                                        Shareholder Services 

...............

註：此處（+）為串連符，放在等號左邊代表右串連（相當於RIGHT JOIN），放在等號右邊代表左串連（相當於LEFT JOIN）。

此處為右串連，表示尋找所有部門表ID和部門名稱以及員工表中對應部門ID的員工姓名，沒有的返回NULL。

  d. Self   join  自串連

 SQL> select worker.last_name || 'works for '|| manager.last_name
  2  from employees worker, employees manager
  3  where  worker.manager_id = manager.employee_id;

  WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME
------------------------------------------------------------
OConnellworks for Mourgos
Grantworks for Mourgos
Whalenworks for Kochhar
Hartsteinworks for King
Fayworks for Hartstein
Mavrisworks for Kochhar

............

2）符合1999規範的串連：

Cross joins 交叉串連
Natural joins  自然串連
Using clause   使用條件
Full or two sided outer joins 完全串連
Arbitrary join conditions for outer joins 為外串連任意加入條件