SQL JOIN 簡單介紹

jopen 10年前發布 | 16K 次閱讀 SQL

為什么需要join

為什么需要join?join中文意思為連接，連接意味著關聯即將一個表和多個表之間關聯起來。在處理數據庫表的時候，我們經常會發現，需要從多個表中獲取信息，將多個表的多個字段數據組裝起來再返回給調用者。所以join的前提是這些表之間必須有關聯字段。

join的分類

SQL 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兩張表，我們可以看到有三種情形：

  1. person表中的人購買了商品，也就是order表中有關于該用戶的商品購買記錄，我們可以從該表中查詢到該用戶買了哪些商品，如andy Chen購買了book和phone兩種商品，即pid在tb_person和tb_order兩種表中都存在；

    </li>

  2. person表中的人未購買商品，如irri Wan和abby Sun兩位用戶并未購買任何商品，即pid只存在于tb_person表；

    </li>

  3. order表中購買商品的用戶在person表中找不到記錄，如pid為4的用戶購買了一臺computer但在tb_person表中沒有該用戶的記錄，即pid只存在于tb_order表；

    </li> </ol>

    理解上述三種情形對于我們理解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;

     

    +-----+-----------+-------+
| 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的結果。

    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);</pre>來自：http://my.oschina.net/gschen/blog/229034
 本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！