程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 詳解Oracle多種表連接方式

詳解Oracle多種表連接方式

編輯:Oracle數據庫基礎

1. 內連接(自然連接)

2. 外連接

(1)左外連接 (左邊的表不加限制)

(2)右外連接(右邊的表不加限制)

(3)全外連接(左右兩表都不加限制)

3. 自連接(同一張表內的連接)

SQL的標准語法:

  1. select table1.column,table2.column 
  2. from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2; 

inner join 表示內連接;
left join表示左外連接;
right join表示右外連接;
full join表示完全外連接;
on子句 用於指定連接條件。

注意:

如果使用from子句指定內、外連接,則必須要使用on子句指定連接條件;

如果使用(+)操作符指定外連接,則必須使用where子句指定連接條件。

一. 內連接(Inner Join/Join)

1.1  Inner Join

Inner join邏輯運算符返回滿足第一個(頂端)輸入與第二個(底端)輸入聯接的每一行。這個和用select查詢多表是一樣的效果,所以內連接用的很少。
還有一點要說明的就是Join 默認就是inner join。 所以我們在寫內連接的時候可以省略inner 這個關鍵字。

1.2 下面舉例來說明內連接:

1.2.1 先創建2張測試表並插入數據:

  1. SQL> select * from dave;  
  2. ID  NAME 
  3. ---------- ----------  
  4. 1  dave  
  5. 2  bl  
  6. 1  bl  
  7. 2  dave  
  8.  
  9.  
  10. SQL> select * from bl;  
  11. ID  NAME 
  12. ---------- ----------  
  13. 1  dave  
  14. 2  bl 

1.2.3 用內鏈接進行查詢:

  1. SQL> Select a.id,a.name,b.name from dave a inner join bl b on a.id=b.id;   -- 標准寫法  
  2. ID NAME       NAME 
  3. ---------- ---------- ----------  
  4. 1 dave       dave  
  5. 2 bl         bl  
  6. 1 bl         dave  
  7. 2 dave       bl  
  8.  
  9.  
  10. SQL> Select a.id,a.name,b.name from dave a join bl b on a.id=b.id;  -- 這裡省略了inner 關鍵字  
  11. ID NAME       NAME 
  12. ---------- ---------- ----------  
  13. 1 dave       dave  
  14. 2 bl         bl  
  15. 1 bl         dave  
  16. 2 dave       bl  
  17.  
  18.  
  19. SQL> Select a.id,a.name,b.name from dave a,bl b where a.id=b.id;  -- select 多表查詢  
  20. ID NAME       NAME 
  21. ---------- ---------- ----------  
  22. 1 dave       dave  
  23. 2 bl         bl  
  24. 1 bl         dave  
  25. 2 dave       bl 

從這三個SQL 的結果我們也可以看出,他們的作用是一樣的。

1.3 自然連接(Natural join)

自然連接是在兩張表中尋找那些數據類型和列名都相同的字段,然後自動地將他們連接起來,並返回所有符合條件按的結果。

先看一下自然連接的例子:

  1. SQL> Select id,name from dave a natural join bl b;  
  2. ID NAME 
  3. ---------- ----------  
  4. 1 dave  
  5. 2 bl 

這裡我們並沒有指定連接的條件,實際上Oracle為我們自作主張的將,dave表中的id和name字段與bl表中的id和name字段進行了連接。也就是實際上相當於

  1. SQL> Select dave.id,bl.name 
  2. From dave join bl on dave.id = bl.id and dave.name=bl.name;  
  3. ID NAME 
  4. ---------- ----------  
  5. 1 dave  
  6. 2 bl 

因此,我們也可以將自然連接理解為內連接的一種。

有關自然連接的一些注意事項:

(1).如果做自然連接的兩個表的有多個字段都滿足有相同名稱和類型,那麼他們會被作為自然連接的條件。

(2).如果自然連接的兩個表僅是字段名稱相同,但數據類型不同,那麼將會返回一個錯誤。

二. 外連接(Outer Join)

outer join則會返回每個滿足第一個(頂端)輸入與第二個(底端)輸入的聯接的行。它還返回任何在第二個輸入中沒有匹配行的第一個輸入中的行。外連接分為三種: 左外連接,右外連接,全外連接。 對應SQL:LEFT/RIGHT/FULL OUTER JOIN。 通常我們省略outer 這個關鍵字。 寫成:LEFT/RIGHT/FULL JOIN。

在左外連接和右外連接時都會以一張表為基表,該表的內容會全部顯示,然後加上兩張表匹配的內容。 如果基表的數據在另一張表沒有記錄。 那麼在相關聯的結果集行中列顯示為空值(NULL)。

對於外連接, 也可以使用“(+) ”來表示。 關於使用(+)的一些注意事項:

1.(+)操作符只能出現在where子句中,並且不能與outer join語法同時使用。

2. 當使用(+)操作符執行外連接時,如果在where子句中包含有多個條件,則必須在所有條件中都包含(+)操作符

3.(+)操作符只適用於列,而不能用在表達式上。

4.(+)操作符不能與or和in操作符一起使用。

5.(+)操作符只能用於實現左外連接和右外連接,而不能用於實現完全外連接。 

在做實驗之前,我們先將dave表和bl裡加一些不同的數據。 以方便測試。

  1. SQL> select * from bl;  
  2.         ID NAME 
  3. ---------- ----------  
  4.          1 dave  
  5.          2 bl  
  6.          3 big bird  
  7.          4 exc  
  8.          9 懷寧  
  9. SQL> select * from dave;  
  10.         ID NAME 
  11. ---------- ----------  
  12.          8 安慶  
  13.          1 dave  
  14.          2 bl  
  15.          1 bl  
  16.          2 dave  
  17.          3 dba  
  18.          4 sf-express  
  19.          5 dmm 

2.1 左外連接(Left outer join/ left join)

left join是以左表的記錄為基礎的,示例中Dave可以看成左表,BL可以看成右表,它的結果集是Dave表中的數據,在加上Dave表和BL表匹配的數據。換句話說,左表(Dave)的記錄將會全部表示出來,而右表(BL)只會顯示符合搜索條件的記錄。BL表記錄不足的地方均為NULL.

示例:

  1. SQL> select * from dave a left join bl b on a.id = b.id;  
  2.        ID NAME               ID NAME 
  3. --------- ---------- ---------- ----------  
  4.         1 bl                  1 dave  
  5.         1 dave                1 dave  
  6.         2 dave                2 bl  
  7.         2 bl                  2 bl  
  8.         3 dba                 3 big bird  
  9.         4 sf-express          4 exc  
  10.         5 dmm                             -- 此處B表為null,因為沒有匹配到  
  11.         8 安慶                             -- 此處B表為null,因為沒有匹配到  
  12.  
  13. SQL> select * from dave a left outer join bl b on a.id = b.id;  
  14.         ID NAME               ID NAME 
  15. ---------- ---------- ---------- ----------  
  16.          1 bl                  1 dave  
  17.          1 dave                1 dave  
  18.          2 dave                2 bl  
  19.          2 bl                  2 bl  
  20.          3 dba                 3 big bird  
  21.          4 sf-express          4 exc  
  22.          5 dmm  
  23.          8 安慶  

用(+)來實現, 這個+號可以這樣來理解: + 表示補充,即哪個表有加號,這個表就是匹配表。所以加號寫在右表,左表就是全部顯示,故是左連接。

  1. SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意: 用(+) 就要用關鍵字where  
  2.        ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          1 bl                  1 dave  
  5.          1 dave                1 dave  
  6.          2 dave                2 bl  
  7.          2 bl                  2 bl  
  8.          3 dba                 3 big bird  
  9.          4 sf-express          4 exc  
  10.          5 dmm  
  11.          8 安慶  

2.2 右外連接(right outer join/ right join)

和left join的結果剛好相反,是以右表(BL)為基礎的, 顯示BL表的所以記錄,在加上Dave和BL 匹配的結果。 Dave表不足的地方用NULL填充.

示例:

  1. SQL> select * from dave a right join bl b on a.id = b.id;  
  2.         ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          1 dave                1 dave  
  5.          2 bl                  2 bl  
  6.          1 bl                  1 dave  
  7.          2 dave                2 bl  
  8.          3 dba                 3 big bird  
  9.          4 sf-express          4 exc  
  10.                                9 懷寧    --此處左表不足用Null 填充 

已選擇7行。

  1. SQL> select * from dave a right outer join bl b on a.id = b.id;  
  2.         ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          1 dave                1 dave  
  5.          2 bl                  2 bl  
  6.          1 bl                  1 dave  
  7.          2 dave                2 bl  
  8.          3 dba                 3 big bird  
  9.          4 sf-express          4 exc  
  10.                                9 懷寧  --此處左表不足用Null 填充 

已選擇7行。  

用(+)來實現, 這個+號可以這樣來理解: + 表示補充,即哪個表有加號,這個表就是匹配表。所以加號寫在左表,右表就是全部顯示,故是右連接。

  1. SQL> Select * from dave a,bl b where a.id(+)=b.id;  
  2.         ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          1 dave                1 dave  
  5.          2 bl                  2 bl  
  6.          1 bl                  1 dave  
  7.          2 dave                2 bl  
  8.          3 dba                 3 big bird  
  9.          4 sf-express          4 exc  
  10.                               9 懷寧  

2.3 全外連接(full outer join/ full join)

左表和右表都不做限制,所有的記錄都顯示,兩表不足的地方用null 填充。 全外連接不支持(+)這種寫法。

示例:

  1. SQL> select * from dave a full join bl b on a.id = b.id;  
  2.         ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          8 安慶  
  5.          1 dave                1 dave  
  6.          2 bl                  2 bl  
  7.          1 bl                  1 dave  
  8.          2 dave                2 bl  
  9.          3 dba                 3 big bird  
  10.          4 sf-express          4 exc  
  11.          5 dmm  
  12.                                9 懷寧 

已選擇9行。

  1. SQL> select * from dave a full outer join bl b on a.id = b.id;  
  2.         ID NAME               ID NAME 
  3. ---------- ---------- ---------- ----------  
  4.          8 安慶  
  5.          1 dave                1 dave  
  6.          2 bl                  2 bl  
  7.          1 bl                  1 dave  
  8.          2 dave                2 bl  
  9.          3 dba                 3 big bird  
  10.          4 sf-express          4 exc  
  11.          5 dmm  
  12.                                9 懷寧 

已選擇9行。

三. 自連接

自連接(self join)是SQL語句中經常要用的連接方式,使用自連接可以將自身表的一個鏡像當作另一個表來對待,從而能夠得到一些特殊的數據。

示例:

在Oracle的scott的schema中有一個表是emp。在emp中的每一個員工都有自己的mgr(經理),並且每一個經理自身也是公司的員工,自身也有自己的經理。

下面我們需要將每一個員工自己的名字和經理的名字都找出來。這時候我們該怎麼做呢?

如果我們有兩張這樣的表分別教worker和mgr,那麼我們就很好寫SQL語句。

Select worker.name,

Mgr.name

From worker,mgr

Where worker.id = mgr.id;

但現在我們只有一張emp表。所以我們可以采用自連接。自連接的本意就是將一張表看成多張表來做連接。我們可以這樣來寫SQL語句:

  1. SQL> select work.ename worker,mgr.ename  manager from scott.emp work, scott.emp mgr  
  2.   2  where work.mgr = mgr.empno(+)  
  3.   3  order by work.ename;  
  4. WORKER     MANAGER  
  5. ---------- ----------  
  6. ADAMS      SCOTT  
  7. ALLEN      BLAKE  
  8. BLAKE      KING  
  9. CLARK      KING  
  10. FORD       JONES  
  11. JAMES      BLAKE  
  12. JONES      KING  
  13. KING                                  --此處右表不足用Null 填充  
  14. MARTIN     BLAKE  
  15. MILLER     CLARK  
  16. SCOTT      JONES  
  17. SMITH      FORD  
  18. TURNER     BLAKE  
  19. WARD       BLAKE 

已選擇14行。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved