mysql資料庫表間內外連結詳解

來源:互聯網
上載者:User

標籤:http   使用   strong   資料   for   art   ar   div   

1. 內串連(自然串連)

2. 外串連

(1)左外串連 (左邊的表不加限制)
(2)右外串連(右邊的表不加限制)
(3)全外串連(左右兩表都不加限制)

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

SQL的標準文法:
select table1.column,table2.column from table1 [inner | left | right | full ] join table2 ontable1.column1 = table2.column2;

 

inner join 表示內串連;

left join表示左外串連;

right join表示右外串連;

full join表示完全外串連;

on子句用於指定串連條件。

注意:

如果使用from子句指定內、外串連,則必須要使用on子句指定串連條件;
如果使用(+)操作符指定外串連,則必須使用where子句指定串連條件。

  • 內串連(InnerJoin/Join

1.1 Inner Join

Inner join邏輯運算子返回滿足第一個(頂端)輸入與第二個(底端)輸入聯結的每一行。這個和用select查詢多表是一樣的效果,所以內串連用的很少。

還有一點要說明的就是Join 預設就是inner join。所以我們在寫內串連的時候可以省略inner 這個關鍵字。

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

1.2.1 先建立2張測試表並插入資料:

SQL> select * from dave;

ID NAME

---------- ----------

1 dave

2 bl

1 bl

2 dave

SQL> select * from bl;

ID NAME

---------- ----------

1 dave

2 bl

1.2.3 用內連結進行查詢:

SQL> Select a.id,a.name,b.name from davea inner join bl b on a.id=b.id; -- 標準寫法

ID NAME NAME

---------- ---------- ----------

1 dave dave

2 bl bl

1 bl dave

2 dave bl

SQL> Select a.id,a.name,b.name from davea join bl b on a.id=b.id; -- 這裡省略了inner關鍵字

ID NAME NAME

---------- ---------- ----------

1 dave dave

2 bl bl

1 bl dave

2 dave bl

SQL> Select a.id,a.name,b.name from davea,bl b where a.id=b.id; -- select 多表查詢

ID NAME NAME

---------- ---------- ----------

1 dave dave

2 bl bl

1 bl dave

2 dave bl

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

1.3 自然串連(Natural join)

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

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

SQL> Select id,name from dave a naturaljoin bl b;

ID NAME

---------- ----------

1 dave

2 bl

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

SQL> Select dave.id,bl.name From davejoin bl on dave.id = bl.id and dave.name=bl.name;

ID NAME

---------- ----------

1 dave

2 bl

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

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

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

(2).如果自然串連的兩個表僅是欄位名稱相同,但資料類型不同,那麼將會返回一個錯誤。

  • 外串連(OuterJoin)

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

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

對於外串連, 也可以使用“(+) ”來表示。 關於使用(+)的一些注意事項:
1.(+)操作符只能出現在where子句中,並且不能與outer join文法同時使用。
2. 當使用(+)操作符執行外串連時,如果在where子句中包含有多個條件,則必須在所有條件中都包含(+)操作符
3.(+)操作符只適用於列,而不能用在運算式上。
4.(+)操作符不能與or和in操作符一起使用。
5.(+)操作符只能用於實現左外串連和右外串連,而不能用於實現完全外串連。
在做實驗之前,我們先將dave表和bl裡加一些不同的資料。以方便測試。

SQL> select * from bl;

ID NAME

---------- ----------

1 dave

2 bl

3 big bird

4 exc

9 懷寧

SQL> select * from dave;

ID NAME

---------- ----------

8 安慶

1 dave

2 bl

1 bl

2 dave

3 dba

4 sf-express

5 dmm

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

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

樣本:

SQL> select *from dave a left join bl b on a.id = b.id;

ID NAME ID NAME

--------- ---------- ---------- ----------

1 bl 1 dave

1 dave 1 dave

2 dave 2 bl

2 bl 2 bl

3 dba 3 big bird

4 sf-express 4 exc

5 dmm -- 此處B表為null,因為沒有匹配到

8 安慶 -- 此處B表為null,因為沒有匹配到

SQL> select *from dave a left outer join bl b on a.id = b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

1 bl 1 dave

1 dave 1 dave

2 dave 2 bl

2 bl 2 bl

3 dba 3 big bird

4 sf-express 4 exc

5 dmm

8 安慶

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

SQL> Select *from dave a,bl b where a.id=b.id(+); -- 注意: 用(+) 就要用關鍵字where

ID NAME ID NAME

---------- ---------- ---------- ----------

1 bl 1 dave

1 dave 1 dave

2 dave 2 bl

2 bl 2 bl

3 dba 3 big bird

4 sf-express 4 exc

5 dmm

8 安慶

2.2 右外串連(rightouter join/ right join)

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

樣本:

SQL> select * from dave a right join bl bon a.id = b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

1 dave 1 dave

2 bl 2 bl

1 bl 1 dave

2 dave 2 bl

3 dba 3 big bird

4 sf-express 4 exc

9 懷寧 --此處左表不足用Null填充

已選擇7行。

SQL> select * from dave a right outerjoin bl b on a.id = b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

1 dave 1 dave

2 bl 2 bl

1 bl 1 dave

2 dave 2 bl

3 dba 3 big bird

4 sf-express 4 exc

9 懷寧 --此處左表不足用Null填充

已選擇7行。

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

SQL> Select * from dave a,bl b wherea.id(+)=b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

 

1 dave 1 dave

2 bl 2 bl

1 bl 1 dave

2 dave 2 bl

3 dba 3 big bird

4 sf-express 4 exc

9 懷寧

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

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

樣本:

SQL> select * from dave a full join bl bon a.id = b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

8 安慶

1 dave 1 dave

2 bl 2 bl

1 bl 1 dave

2 dave 2 bl

3 dba 3 big bird

4 sf-express 4 exc

5 dmm

9 懷寧

已選擇9行。

SQL> select * from dave a full outer joinbl b on a.id = b.id;

ID NAME ID NAME

---------- ---------- ---------- ----------

8 安慶

1 dave 1 dave

2 bl 2 bl

1 bl 1 dave

2 dave 2 bl

3 dba 3 big bird

4 sf-express 4 exc

5 dmm

9 懷寧

已選擇9行。

  • 自串連

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

樣本:

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

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

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

Select worker.name,Mgr.name From worker,mgrWhere worker.id = mgr.id;

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

SQL> select work.enameworker,mgr.ename manager from scott.emp work, scott.emp mgr

2 where work.mgr= mgr.empno

3 order by work.ename;

WORKER MANAGER

---------- ----------

ADAMS SCOTT

ALLEN BLAKE

BLAKE KING

CLARK KING

FORD JONES

JAMES BLAKE

JONES KING

MARTIN BLAKE

MILLER CLARK

SCOTT JONES

SMITH FORD

 

WORKER MANAGER

---------- ----------

TURNER BLAKE

WARD BLAKE

已選擇13行。

各種串連的一個圖示:

 
相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.