MySQL數據庫連接查詢 join原理

1、連接查詢的分類

  • 交叉連接
  • 內連接
  • 外連接
    • 左外鏈接(左連接)
    • 右外連接(右連接)
  • 自然連接

2、交叉連接

將兩張表的數據與另外一張表彼此交叉

2.1、原理

笛卡爾積:

  • 從第一張表一次取出每一條數據
  • 取出每一條記錄之後,與另外一張表的全部記錄挨個匹配
  • 沒有任何匹配條件,所有的結果都會保留
  • 記錄數=第一張表記錄數 * 第二張表記錄數
  • 字段數 = 第一張表字段數 + 第二章表字段數

2.2、基本語法

表1 cross join 表2;
mysql> select * from tb_teacher;
+--------+------+
| name   | age  |
+--------+------+
| Jack   |   24 |
| Tom    |   26 |
| Steve  | NULL |
| 張三   |   23 |
| 張三   |   23 |
+--------+------+
5 rows in set (0.00 sec)

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 張飛   |        2 |   21 |      1 |
|  8 | 關羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
6 rows in set (0.01 sec)

mysql> select * from my_student cross join tb_teacher;
+----+--------+----------+------+--------+--------+------+
| id | name   | class_id | age  | gender | name   | age  |
+----+--------+----------+------+--------+--------+------+
|  1 | 劉備   |        1 |   18 |      2 | Jack   |   24 |
|  1 | 劉備   |        1 |   18 |      2 | Tom    |   26 |
|  1 | 劉備   |        1 |   18 |      2 | Steve  | NULL |
|  1 | 劉備   |        1 |   18 |      2 | 張三   |   23 |
|  1 | 劉備   |        1 |   18 |      2 | 張三   |   23 |
|  2 | 李四   |        1 |   19 |      1 | Jack   |   24 |
|  2 | 李四   |        1 |   19 |      1 | Tom    |   26 |
|  2 | 李四   |        1 |   19 |      1 | Steve  | NULL |
|  2 | 李四   |        1 |   19 |      1 | 張三   |   23 |
|  2 | 李四   |        1 |   19 |      1 | 張三   |   23 |
|  3 | 王五   |        2 |   20 |      2 | Jack   |   24 |
|  3 | 王五   |        2 |   20 |      2 | Tom    |   26 |
|  3 | 王五   |        2 |   20 |      2 | Steve  | NULL |
|  3 | 王五   |        2 |   20 |      2 | 張三   |   23 |
|  3 | 王五   |        2 |   20 |      2 | 張三   |   23 |
|  7 | 張飛   |        2 |   21 |      1 | Jack   |   24 |
|  7 | 張飛   |        2 |   21 |      1 | Tom    |   26 |
|  7 | 張飛   |        2 |   21 |      1 | Steve  | NULL |
|  7 | 張飛   |        2 |   21 |      1 | 張三   |   23 |
|  7 | 張飛   |        2 |   21 |      1 | 張三   |   23 |
|  8 | 關羽   |        1 |   22 |      2 | Jack   |   24 |
|  8 | 關羽   |        1 |   22 |      2 | Tom    |   26 |
|  8 | 關羽   |        1 |   22 |      2 | Steve  | NULL |
|  8 | 關羽   |        1 |   22 |      2 | 張三   |   23 |
|  8 | 關羽   |        1 |   22 |      2 | 張三   |   23 |
|  9 | 曹操   |        1 |   20 |   NULL | Jack   |   24 |
|  9 | 曹操   |        1 |   20 |   NULL | Tom    |   26 |
|  9 | 曹操   |        1 |   20 |   NULL | Steve  | NULL |
|  9 | 曹操   |        1 |   20 |   NULL | 張三   |   23 |
|  9 | 曹操   |        1 |   20 |   NULL | 張三   |   23 |
+----+--------+----------+------+--------+--------+------+
30 rows in set (0.00 sec)

2.3、應用

基本沒有實際意義

等價於

select * from my_student, tb_teacher;

3、內連接

從一張表中取出所有的記錄,去另外一張表中匹配,利用匹配條件進行匹配,成功則保留,失敗則放棄

3.1、原理

  • 從第一張表中取出一條記錄,然後去另外一張表中進行匹配
  • 利用匹配條件進行匹配
  • 匹配到則保留,繼續向下匹配
  • 匹配失敗則放棄

3.2、基本語法

表1 inner join 表2 on 匹配條件
create table my_class(
    id int primary key auto_increment,
    name varchar(10) not null
);

insert into my_class (name) values ('一班'), ('二班');

mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
+----+--------+

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 張飛   |        2 |   21 |      1 |
|  5 | 關羽   |        1 |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

-- 如果內連接沒有條件,其實就是交叉連接(笛卡爾積)
mysql> select * from my_student inner join my_class;
+----+--------+----------+------+--------+----+--------+
| id | name   | class_id | age  | gender | id | name   |
+----+--------+----------+------+--------+----+--------+
|  1 | 劉備   |        1 |   18 |      2 |  1 | 一班   |
|  1 | 劉備   |        1 |   18 |      2 |  2 | 二班   |
|  2 | 李四   |        1 |   19 |      1 |  1 | 一班   |
|  2 | 李四   |        1 |   19 |      1 |  2 | 二班   |
|  3 | 王五   |        2 |   20 |      2 |  1 | 一班   |
|  3 | 王五   |        2 |   20 |      2 |  2 | 二班   |
|  4 | 張飛   |        2 |   21 |      1 |  1 | 一班   |
|  4 | 張飛   |        2 |   21 |      1 |  2 | 二班   |
|  5 | 關羽   |        1 |   22 |      2 |  1 | 一班   |
|  5 | 關羽   |        1 |   22 |      2 |  2 | 二班   |
|  6 | 曹操   |        1 |   20 |   NULL |  1 | 一班   |
|  6 | 曹操   |        1 |   20 |   NULL |  2 | 二班   |
+----+--------+----------+------+--------+----+--------+
12 rows in set (0.00 sec)

-- 表的設計,通常會有同名字段,通常使用`表名.字段`來保證唯一性
mysql> select * from my_student inner join my_class on my_student.class_id = my_class.id;
+----+--------+----------+------+--------+----+--------+
| id | name   | class_id | age  | gender | id | name   |
+----+--------+----------+------+--------+----+--------+
|  1 | 劉備   |        1 |   18 |      2 |  1 | 一班   |
|  2 | 李四   |        1 |   19 |      1 |  1 | 一班   |
|  3 | 王五   |        2 |   20 |      2 |  2 | 二班   |
|  4 | 張飛   |        2 |   21 |      1 |  2 | 二班   |
|  5 | 關羽   |        1 |   22 |      2 |  1 | 一班   |
|  6 | 曹操   |        1 |   20 |   NULL |  1 | 一班   |
+----+--------+----------+------+--------+----+--------+

-- 如果表名比較長,可以使用別名簡化
mysql> select * from my_student as a inner join my_class b on a.class_id = b.id;
+----+--------+----------+------+--------+----+--------+
| id | name   | class_id | age  | gender | id | name   |
+----+--------+----------+------+--------+----+--------+
|  1 | 劉備   |        1 |   18 |      2 |  1 | 一班   |
|  2 | 李四   |        1 |   19 |      1 |  1 | 一班   |
|  3 | 王五   |        2 |   20 |      2 |  2 | 二班   |
|  4 | 張飛   |        2 |   21 |      1 |  2 | 二班   |
|  5 | 關羽   |        1 |   22 |      2 |  1 | 一班   |
|  6 | 曹操   |        1 |   20 |   NULL |  1 | 一班   |
+----+--------+----------+------+--------+----+--------+

-- 可以交換兩張表的先後順序
mysql> select * from my_class b inner join my_student as a on a.class_id = b.id;
+----+--------+----+--------+----------+------+--------+
| id | name   | id | name   | class_id | age  | gender |
+----+--------+----+--------+----------+------+--------+
|  1 | 一班   |  1 | 劉備   |        1 |   18 |      2 |
|  1 | 一班   |  2 | 李四   |        1 |   19 |      1 |
|  2 | 二班   |  3 | 王五   |        2 |   20 |      2 |
|  2 | 二班   |  4 | 張飛   |        2 |   21 |      1 |
|  1 | 一班   |  5 | 關羽   |        1 |   22 |      2 |
|  1 | 一班   |  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----+--------+----------+------+--------+

-- on 可以使用 where 替換,推薦使用 on
mysql> select * from my_class b inner join my_student as a where a.class_id = b.id;
+----+--------+----+--------+----------+------+--------+
| id | name   | id | name   | class_id | age  | gender |
+----+--------+----+--------+----------+------+--------+
|  1 | 一班   |  1 | 劉備   |        1 |   18 |      2 |
|  1 | 一班   |  2 | 李四   |        1 |   19 |      1 |
|  2 | 二班   |  3 | 王五   |        2 |   20 |      2 |
|  2 | 二班   |  4 | 張飛   |        2 |   21 |      1 |
|  1 | 一班   |  5 | 關羽   |        1 |   22 |      2 |
|  1 | 一班   |  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----+--------+----------+------+--------+

3.3、應用

內連接通常是在對數據有精確要求的地方使用,必須保證兩張表中都能進行數據匹配,內連接匹配到才會保存

4、外連接

按照某一張表作為主表(表中所有記錄在最後都會保留)根據條件取連接另外一張表,從而得到目標數據

外連接分為兩種:

  • 左連接:左表是主表
  • 右連接:右表是主表

4.1、原理

  • 確定主表,左連接就是左邊的表為主表,右連接就是右邊的表為主表
  • 拿主表的每一條記錄,去匹配另外的一張表(從表)的每一條記錄
  • 如果滿足匹配條件,保留,不滿足即不保留
  • 如果主表記錄在從表中一條都沒有匹配成功,那麼也要保留記錄, 從表對應的字段值都是null

4.2、基本語法

-- 左連接
主表 left join 從表 on 連接條件;
-- 右連接
從表 right join 主表 on 連接條件;

左表的數據在前,右表的數據在後

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 張飛   |        2 |   21 |      1 |
|  5 | 關羽   |        1 |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  3 | 三班   |
|  2 | 二班   |
+----+--------+
mysql> select * from my_student as s left join my_class c on s.class_id = c.id;
+----+--------+----------+------+--------+------+--------+
| id | name   | class_id | age  | gender | id   | name   |
+----+--------+----------+------+--------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |    1 | 一班   |
|  2 | 李四   |        1 |   19 |      1 |    1 | 一班   |
|  3 | 王五   |        2 |   20 |      2 |    2 | 二班   |
|  4 | 張飛   |        2 |   21 |      1 |    2 | 二班   |
|  5 | 關羽   |        1 |   22 |      2 |    1 | 一班   |
|  6 | 曹操   |        1 |   20 |   NULL |    1 | 一班   |
+----+--------+----------+------+--------+------+--------+
select * from my_student as s right join my_class c on s.class_id = c.id;
mysql> select * from my_student as s right join my_class c on s.class_id = c.id;
+------+--------+----------+------+--------+----+--------+
| id   | name   | class_id | age  | gender | id | name   |
+------+--------+----------+------+--------+----+--------+
|    1 | 劉備   |        1 |   18 |      2 |  1 | 一班   |
|    2 | 李四   |        1 |   19 |      1 |  1 | 一班   |
|    3 | 王五   |        2 |   20 |      2 |  2 | 二班   |
|    4 | 張飛   |        2 |   21 |      1 |  2 | 二班   |
|    5 | 關羽   |        1 |   22 |      2 |  1 | 一班   |
|    6 | 曹操   |        1 |   20 |   NULL |  1 | 一班   |
| NULL | NULL   |     NULL | NULL |   NULL |  3 | 三班   |
+------+--------+----------+------+--------+----+--------+

4.3、特點

外連接中主表的數據一定會保存,連接之後不會出現記錄數少於主表(內連接可能少數據)

左連接和右連接可以相互轉換,但是數據對應的位置(字段順序)會改變

4.4、應用

獲取對應主表以及其他數據(關聯)

通常使用左連接

5、using關鍵字

在連接查詢中用來代替對應on關鍵字進行條件匹配

5.1、原理

  • 在連接查詢時,使用on的地方用using代替
  • 使用using的前提是對應的兩張表連接的字段名是同名的(類似自然連接)
  • 如果使用using關鍵字,那麼對應的同名字段,最後在結果中隻會保留一個

5.2、基本語法

表1 [inner, left, right] join 表2 using (同名字段列表);
select * from my_student left join my_class using(class_id);
-- 等價於
select * from my_student left join my_class on my_student.class_id = my_class.class_id;

這個方法通常不使用

到此這篇關於MySQL數據庫連接查詢 join原理的文章就介紹到這瞭,更多相關MySQL連接查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: