搜尋

首頁 >資料庫 >mysql教程 >MySql中如何使用JOIN

MySql中如何使用JOIN

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB轉載
2023-06-04 08:02:021891瀏覽

JOIN的意思就如英文單字「join」一樣，連接兩張表，大致分為內連接，外連接，右連接，左連接，自然連接。

MySql中如何使用JOIN

先建立兩個表，以下用於範例

    CREATE TABLE t_blog(
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(50),
        typeId INT
    );
    SELECT * FROM t_blog;
    +----+-------+--------+
    | id | title | typeId |
    +----+-------+--------+
    |  1 | aaa   |      1 |
    |  2 | bbb   |      2 |
    |  3 | ccc   |      3 |
    |  4 | ddd   |      4 |
    |  5 | eee   |      4 |
    |  6 | fff   |      3 |
    |  7 | ggg   |      2 |
    |  8 | hhh   |   NULL |
    |  9 | iii   |   NULL |
    | 10 | jjj   |   NULL |
    +----+-------+--------+
    -- 博客的类别
    CREATE TABLE t_type(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20)
    );
    SELECT * FROM t_type;
    +----+------------+
    | id | name       |
    +----+------------+
    |  1 | C++        |
    |  2 | C          |
    |  3 | Java       |
    |  4 | C#         |
    |  5 | Javascript |
    +----+------------+

笛卡爾積：CROSS JOIN

要理解各種JOIN首先要理解笛卡兒積。笛卡爾積是將A表中的每個記錄與B表中的每個記錄組合。因此，當A表中有n筆記錄且B表中有m筆記錄時，笛卡爾積操作所得到的結果將會有n*m筆記錄。下面的例子，t_blog有10筆記錄，t_type有5筆記錄，所有他們兩個的笛卡爾積有50筆記錄。有五種產生笛卡兒積的方式如下。 

SELECT * FROM t_blog CROSS JOIN t_type;
    SELECT * FROM t_blog INNER JOIN t_type;
    SELECT * FROM t_blog,t_type;
    SELECT * FROM t_blog NATURE JOIN t_type;
    select * from t_blog NATURA join t_type;
    +----+-------+--------+----+------------+
    | id | title | typeId | id | name       |
    +----+-------+--------+----+------------+
    |  1 | aaa   |      1 |  1 | C++        |
    |  1 | aaa   |      1 |  2 | C          |
    |  1 | aaa   |      1 |  3 | Java       |
    |  1 | aaa   |      1 |  4 | C#         |
    |  1 | aaa   |      1 |  5 | Javascript |
    |  2 | bbb   |      2 |  1 | C++        |
    |  2 | bbb   |      2 |  2 | C          |
    |  2 | bbb   |      2 |  3 | Java       |
    |  2 | bbb   |      2 |  4 | C#         |
    |  2 | bbb   |      2 |  5 | Javascript |
    |  3 | ccc   |      3 |  1 | C++        |
    |  3 | ccc   |      3 |  2 | C          |
    |  3 | ccc   |      3 |  3 | Java       |
    |  3 | ccc   |      3 |  4 | C#         |
    |  3 | ccc   |      3 |  5 | Javascript |
    |  4 | ddd   |      4 |  1 | C++        |
    |  4 | ddd   |      4 |  2 | C          |
    |  4 | ddd   |      4 |  3 | Java       |
    |  4 | ddd   |      4 |  4 | C#         |
    |  4 | ddd   |      4 |  5 | Javascript |
    |  5 | eee   |      4 |  1 | C++        |
    |  5 | eee   |      4 |  2 | C          |
    |  5 | eee   |      4 |  3 | Java       |
    |  5 | eee   |      4 |  4 | C#         |
    |  5 | eee   |      4 |  5 | Javascript |
    |  6 | fff   |      3 |  1 | C++        |
    |  6 | fff   |      3 |  2 | C          |
    |  6 | fff   |      3 |  3 | Java       |
    |  6 | fff   |      3 |  4 | C#         |
    |  6 | fff   |      3 |  5 | Javascript |
    |  7 | ggg   |      2 |  1 | C++        |
    |  7 | ggg   |      2 |  2 | C          |
    |  7 | ggg   |      2 |  3 | Java       |
    |  7 | ggg   |      2 |  4 | C#         |
    |  7 | ggg   |      2 |  5 | Javascript |
    |  8 | hhh   |   NULL |  1 | C++        |
    |  8 | hhh   |   NULL |  2 | C          |
    |  8 | hhh   |   NULL |  3 | Java       |
    |  8 | hhh   |   NULL |  4 | C#         |
    |  8 | hhh   |   NULL |  5 | Javascript |
    |  9 | iii   |   NULL |  1 | C++        |
    |  9 | iii   |   NULL |  2 | C          |
    |  9 | iii   |   NULL |  3 | Java       |
    |  9 | iii   |   NULL |  4 | C#         |
    |  9 | iii   |   NULL |  5 | Javascript |
    | 10 | jjj   |   NULL |  1 | C++        |
    | 10 | jjj   |   NULL |  2 | C          |
    | 10 | jjj   |   NULL |  3 | Java       |
    | 10 | jjj   |   NULL |  4 | C#         |
    | 10 | jjj   |   NULL |  5 | Javascript |
    +----+-------+--------+----+------------+

內連接：INNER JOIN

內連接INNER JOIN是最常用的連接操作。從數學的角度來說，這就是要計算兩個表的相交部分；而從笛卡兒積的角度來看，就是要從笛卡兒積中篩選出滿足ON子句條件的記錄。有INNER JOIN，WHERE（等值連接），STRAIGHT_JOIN,JOIN(省略INNER)四種寫法。 

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
    SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
    SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
    SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C#   |
    |  5 | eee   |      4 |  4 | C#   |
    +----+-------+--------+----+------+

左連接：LEFT JOIN

左連接LEFT JOIN的意義就是求兩個表的交集外加左表剩下的資料。依舊從笛卡兒積的角度講，就是先從笛卡兒積中挑出ON子句條件成立的記錄，然後加上左表中剩餘的記錄（見最後三條）。 

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+------+------+
    | id | title | typeId | id   | name |
    +----+-------+--------+------+------+
    |  1 | aaa   |      1 |    1 | C++  |
    |  2 | bbb   |      2 |    2 | C    |
    |  7 | ggg   |      2 |    2 | C    |
    |  3 | ccc   |      3 |    3 | Java |
    |  6 | fff   |      3 |    3 | Java |
    |  4 | ddd   |      4 |    4 | C#   |
    |  5 | eee   |      4 |    4 | C#   |
    |  8 | hhh   |   NULL | NULL | NULL |
    |  9 | iii   |   NULL | NULL | NULL |
    | 10 | jjj   |   NULL | NULL | NULL |
    +----+-------+--------+------+------+

右連結：RIGHT JOIN

同理右連結RIGHT JOIN就是求兩個表的交集外加右邊表剩下的資料。再次從笛卡兒積的角度描述，右連接就是從笛卡兒積中挑出ON子句條件成立的記錄，然後加上右表中剩餘的記錄（見最後一條）。 

SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+----+------------+
    | id   | title | typeId | id | name       |
    +------+-------+--------+----+------------+
    |    1 | aaa   |      1 |  1 | C++        |
    |    2 | bbb   |      2 |  2 | C          |
    |    3 | ccc   |      3 |  3 | Java       |
    |    4 | ddd   |      4 |  4 | C#         |
    |    5 | eee   |      4 |  4 | C#         |
    |    6 | fff   |      3 |  3 | Java       |
    |    7 | ggg   |      2 |  2 | C          |
    | NULL | NULL  |   NULL |  5 | Javascript |
    +------+-------+--------+----+------------+

外連接：OUTER JOIN

外連接就是求兩個集合的並集。從笛卡兒積的角度講就是從笛卡兒積中挑出ON子句條件成立的記錄，然後加上左表剩餘的記錄，最後加上右表中剩餘的記錄。 MySQL不支援OUTER JOIN，但是我們可以透過將左連接和右連接的結果進行UNION操作來實現。 

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    UNION
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+------+------------+
    | id   | title | typeId | id   | name       |
    +------+-------+--------+------+------------+
    |    1 | aaa   |      1 |    1 | C++        |
    |    2 | bbb   |      2 |    2 | C          |
    |    7 | ggg   |      2 |    2 | C          |
    |    3 | ccc   |      3 |    3 | Java       |
    |    6 | fff   |      3 |    3 | Java       |
    |    4 | ddd   |      4 |    4 | C#         |
    |    5 | eee   |      4 |    4 | C#         |
    |    8 | hhh   |   NULL | NULL | NULL       |
    |    9 | iii   |   NULL | NULL | NULL       |
    |   10 | jjj   |   NULL | NULL | NULL       |
    | NULL | NULL  |   NULL |    5 | Javascript |
    +------+-------+--------+------+------------+

USING子句

MySQL中連接SQL語句中，ON子句的語法格式為：table1.column_name = table2.column_name。當模式設計對聯接表的欄位採用了相同的命名樣式時，就可以使用 USING 語法來簡化 ON 語法，格式為：USING(column_name)。
所以，USING的功能相當於ON，差別在於USING指定一個屬性名稱用來連接兩個表，而ON指定一個條件。另外，SELECT *時，USING會移除USING指定的列，而ON不會。實例如下。 

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id;
    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C#   |
    |  5 | eee   |      4 |  4 | C#   |
    +----+-------+--------+----+------+
    SELECT * FROM t_blog INNER JOIN t_type USING(typeId);
    ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause'
    SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 应为t_blog的typeId与t_type的id不同名，无法用Using，这里用id代替下。
    +----+-------+--------+------------+
    | id | title | typeId | name       |
    +----+-------+--------+------------+
    |  1 | aaa   |      1 | C++        |
    |  2 | bbb   |      2 | C          |
    |  3 | ccc   |      3 | Java       |
    |  4 | ddd   |      4 | C#         |
    |  5 | eee   |      4 | Javascript |
    +----+-------+--------+------------+

自然連接：NATURE JOIN

自然連接就是USING子句的簡化版，它找出兩個表中相同的列作為連接條件進行連接。有左自然連接，右自然連接和普通自然連接之分。在t_blog和t_type範例中，兩個表格相同的欄位是id，所以會拿id當連接條件。
另外千萬分清下面三條語句的差別 。
自然連結:SELECT * FROM t_blog NATURAL JOIN t_type;
笛卡兒積:SELECT * FROM t_blog NATURA JOIN t_type;
笛卡兒積:SELECT * FROM t_blog NATURE JOIN t_type;##rr

SELECT * FROM t_blog NATURAL JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog,t_type WHERE t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type USING(id);

    +----+-------+--------+------------+
    | id | title | typeId | name       |
    |  1 | aaa   |      1 | C++        |
    |  2 | bbb   |      2 | C          |
    |  3 | ccc   |      3 | Java       |
    |  4 | ddd   |      4 | C#         |
    |  5 | eee   |      4 | Javascript |
    SELECT * FROM t_blog NATURAL LEFT JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type USING(id);
    |  6 | fff   |      3 | NULL       |
    |  7 | ggg   |      2 | NULL       |
    |  8 | hhh   |   NULL | NULL       |
    |  9 | iii   |   NULL | NULL       |
    | 10 | jjj   |   NULL | NULL       |
    SELECT * FROM t_blog NATURAL RIGHT JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type USING(id);
    +----+------------+-------+--------+
    | id | name       | title | typeId |
    |  1 | C++        | aaa   |      1 |
    |  2 | C          | bbb   |      2 |
    |  3 | Java       | ccc   |      3 |
    |  4 | C#         | ddd   |      4 |
    |  5 | Javascript | eee   |      4 |

以上是MySql中如何使用JOIN的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除
上一篇：mysql中int(5)的長度是多少下一篇：mysql中int(5)的長度是多少

相關文章

看更多