mysql join用法簡介

　為什麼需要join

　　為什麼需要join?join中文意思為連接配接，連接配接意味着關聯即将一個表和多個表之間關聯起來。在處理資料庫表的時候，我們經常會發現，需要從多個表中擷取資訊，将多個表的多個字段資料組裝起來再傳回給調用者。是以join的前提是這些表之間必須有關聯字段。

　join的分類

　　join分為兩種，inner join和outer join，其中outer join分為三種，left outer join, right outer join, full outer join，另外left outer join又簡稱為left join即大家所熟知的左連接配接。

　各種join的差別

　　在介紹各種join的差別之前，我們先來看一個簡單的示例：

　　場景描述：

　　網際網路時代，大家都喜歡在網上購物，尤其是淘寶和京東，是以我們選擇的場景也是大家熟悉的網上購物。這是一個關于一個人和他在商城買了什麼商品的一個故事；

　　針對上述需求，我們建立了兩張表，tb_person和tb_order，其中tb_person是關于這個人的描述，tb_order是關于他購買的商品的一個描述。

　　我們的表結構很簡單，tb_person隻需要知道這個人是誰就可以了，是以隻有三個字段id，firstname(名)和lastname(姓)，同樣tb_order也很簡單，我們隻要知道誰買了什麼商品，是以隻需要3個字段，分别是oid, oname(商品名稱), pid(購買者編号)。

　　tb_person:

-----------+-------------+------+-----+---------+----------------+

| Field     | Type        |

Null

Key

Default

| Extra          |

-----------+-------------+------+-----+---------+----------------+

| pid       |

int

(11)     |

NO

| PRI |

NULL

| auto_increment |

| firstname |

varchar

(50) | YES  |     |

NULL

|                |

| lastname  |

varchar

(50) | YES  |     |

NULL

|                |

-----------+-------------+------+-----+---------+----------------+

　　tb_order:

-------+-------------+------+-----+---------+----------------+

| Field | Type        |

Null

Key

Default

| Extra          |

-------+-------------+------+-----+---------+----------------+

| oid   |

int

(11)     |

NO

| PRI |

NULL

| auto_increment |

| oname |

varchar

(50) | YES  |     |

NULL

|                |

| pid   |

int

(11)     | YES  |     |

NULL

|                |

-------+-------------+------+-----+---------+----------------+

　　接下來，我們向上述兩張表中寫入一些示例資料：

　　data in tb_person:

-----+-----------+----------+

| pid | firstname | lastname |

-----+-----------+----------+

|   1 | andy      | chen     |

|   2 | irri      | wan      |

|   3 | abby      | sun      |

-----+-----------+----------+

　　tb_person表中有三位人員，分别是andy Chen, irri Wan, abby Sun；

　　data in tb_order:

-----+----------+------+

| oid | oname    | pid  |

-----+----------+------+

|   1 | book     |    1 |

|   2 | phone    |    1 |

|   3 | computer |    4 |

-----+----------+------+

　　tb_order表中記錄了3條資料，人員編号為1也就是andy Chen買了兩件商品分别是book和phone，另外還有一個人員編号為4的人買了一件商品computer。關于這個大家可能會産生疑問，為什麼tb_person表中沒有人員編号為4的人呢？這裡我們姑且認為由于注冊使用者較多，我們采用了使用者分表政策，是以人員編号為4的使用者可能在另外一張人員表中。

　　從之前的描述我們知道，表與表之間如果要join則必須要有關聯的字段，上述示例我們看到這個關聯的字段就是pid。

　　根據tb_person和tb_order兩張表，我們可以看到有三種情形：

person表中的人購買了商品，也就是order表中有關于該使用者的商品購買記錄，我們可以從該表中查詢到該使用者買了哪些商品，如andy Chen購買了book和phone兩種商品，即pid在tb_person和tb_order兩種表中都存在；
person表中的人未購買商品，如irri Wan和abby Sun兩位使用者并未購買任何商品，即pid隻存在于tb_person表；
order表中購買商品的使用者在person表中找不到記錄，如pid為4的使用者購買了一台computer但在tb_person表中沒有該使用者的記錄，即pid隻存在于tb_order表；

　　了解上述三種情形對于我們了解join有非常大的幫助，接下來我們将具體的分析每種join的差別：

　　INNER JOIN

　　所謂inner join的意思就是我們前面提到的情形1，pid必須在tb_person和tb_order兩張表中同時存在；

MariaDB [demo]>

SELECT

p.pid, p.firstname, o.oname

->

FROM

tb_person p

->

INNER

JOIN

tb_order o

->

ON

p.pid=o.pid;

-----+-----------+-------+

| pid | firstname | oname |

-----+-----------+-------+

|   1 | andy      | book  |

|   1 | andy      | phone |

-----+-----------+-------+

　　LEFT JOIN

　　tb_person LEFT JOIN tb_order的意思是上述情形1，情形2的并集。LEFT JOIN的結果集不僅包含INNER JOIN的結果，而且還包含所有tb_person中沒有購買任何商品的使用者集。

MariaDB [demo]>

SELECT

p.pid, p.firstname, o.oname

->

FROM

tb_person p

->

LEFT

JOIN

tb_order o

->

ON

p.pid=o.pid;

8	`+` `-----+-----------+-------+` `\| pid \| firstname \| oname \|` `+` `-----+-----------+-------+` `\| 1 \| andy \| book \|` `\| 1 \| andy \| phone \|` `\| 2 \| irri \|` `NULL` `\|` `\| 3 \| abby \|` `NULL` `\|` `+` `-----+-----------+-------+`

　　RIGHT JOIN

　　tb_person RIGHT JOIN tb_order的意思是上述情形1和情形3的并集。RIGHT JOIN的結果集不僅包含INNER JOIN的結果，而且還包含所有tb_order中所有已經購買商品的使用者但該使用者記錄不存在于tb_person表。

MariaDB [demo]>

SELECT

p.pid, p.firstname, o.oname

->

FROM

tb_person p

->

RIGHT

JOIN

tb_order o

->

ON

p.pid=o.pid;

------+-----------+----------+

| pid  | firstname | oname    |

------+-----------+----------+

|    1 | andy      | book     |

|    1 | andy      | phone    |

NULL

NULL

| computer |

------+-----------+----------+

　　FULL JOIN

　　故名思議，FULL JOIN就是上述情形1，2，3的并集了，但是mysql資料庫不支援full join查詢，是以我們隻能LEFT JOIN union RIGHT JOIN，才能得到FULL JOIN的結果。

9	`MariaDB [demo]>` `SELECT` `p.pid, p.firstname, o.oname` `->` `FROM` `tb_person p` `->` `LEFT` `JOIN` `tb_order o` `->` `ON` `p.pid=o.pid` `->` `UNION` `->` `SELECT` `p.pid, p.firstname, o.oname` `->` `FROM` `tb_person p` `->` `RIGHT` `JOIN` `tb_order o` `->` `ON` `p.pid=o.pid;`

------+-----------+----------+

| pid  | firstname | oname    |

------+-----------+----------+

|    1 | andy      | book     |

|    1 | andy      | phone    |

|    2 | irri      |

NULL

|    3 | abby      |

NULL

NULL

NULL

| computer |

------+-----------+----------+

　　注：我們上述的sql語句全部基于mysql資料庫執行。

　總結

　　本文主要描述了sql join的分類以及各種join的差別，通過簡單的示例，讓大家更清晰的去了解他們。至于什麼時候使用join要視具體的情況而定，根據不同的需求采用不同的政策。

　　非常感謝大家的熱心回複，可能有些問題的探讨超出了本文的範疇，但是非常樂意大家提出問題，然後大家一起去探索去發現。

　引用

　　NULL

　附件

　　demo.sql檔案

create

database

demo;

use demo;

create

table

tb_person (

pid

int

(11) auto_increment,

firstname

varchar

(50),

lastname

varchar

(50),

primary

key

(pid)

);

create

table

tb_order (

oid

int

(11) auto_increment,

oname

varchar

(50),

pid

int

(11),

primary

key

(oid)

);

insert

into

tb_person(firstname, lastname)

values

'andy'

'chen'

);

insert

into

tb_person(firstname, lastname)

values

'irri'

'wan'

);

insert

into

tb_person(firstname, lastname)

values

'abby'

'sun'

);

insert

into

tb_order(oname, pid)

values

'book'

, 1);

insert

into

tb_order(oname, pid)

values

'phone'

, 1);

insert

into

tb_order(oname, pid)

values

'computer'

, 4);