當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

MySQL 連接查詢的原理和應用

時間：2020-11-22來源：www.farandoo.com作者：電腦系統城

概述

MySQL最強大的功能之一就是能在數據檢索的執行中連接（join）表。大部分的單表數據查詢并不能滿足我們的需求，這時候我們就需要連接一個或者多個表，并通過一些條件過濾篩選出我們需要的數據。

了解MySQL連接查詢之前我們先來理解下笛卡爾積的原理。

數據準備

依舊使用上節的表數據（包含classes 班級表和students 學生表）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 mysql> select * from classes;
+---------+-----------+
| classid | classname |
+---------+-----------+
| 1 | 初三一班 |
| 2 | 初三二班 |
| 3 | 初三三班 |
| 4 | 初三四班 |
+---------+-----------+
4 rows in set

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 19 | lala | 51 | 0 |
+-----------+-------------+-------+---------+
9 rows in set

笛卡爾積

笛卡爾積：也就是笛卡爾乘積，假設兩個集合A和B，笛卡爾積表示A集合中的元素和B集合中的元素任意相互關聯產生的所有可能的結果。

比如A中有m個元素，B中有n個元素，A、B笛卡爾積產生的結果有m*n個結果，相當于循環遍歷兩個集合中的元素，任意組合。

笛卡爾積在SQL中的實現方式既是交叉連接(Cross Join)。所有連接方式都會先生成臨時笛卡爾積表，笛卡爾積是關系代數里的一個概念，表示兩個表中的每一行數據任意組合。

所以上面的表就是 4（班級表）* 9（學生表） = 36條數據；

笛卡爾積語法格式：

1
2
3 select cname1,cname2,... from tname1,tname2,...;
or
select cname from tname1 join tname2 [join tname...];

圖例表示：

上述兩個表實際執行結果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 mysql> select * from classes a,students b order by a.classid,b.studentid;
+---------+-----------+-----------+-------------+-------+---------+
| classid | classname | studentid | studentname | score | classid |
+---------+-----------+-----------+-------------+-------+---------+
| 1 | 初三一班 | 1 | brand | 97.5 | 1 |
| 1 | 初三一班 | 2 | helen | 96.5 | 1 |
| 1 | 初三一班 | 3 | lyn | 96 | 1 |
| 1 | 初三一班 | 4 | sol | 97 | 1 |
| 1 | 初三一班 | 7 | b1 | 81 | 2 |
| 1 | 初三一班 | 8 | b2 | 82 | 2 |
| 1 | 初三一班 | 13 | c1 | 71 | 3 |
| 1 | 初三一班 | 14 | c2 | 72.5 | 3 |
| 1 | 初三一班 | 19 | lala | 51 | 0 |
| 2 | 初三二班 | 1 | brand | 97.5 | 1 |
| 2 | 初三二班 | 2 | helen | 96.5 | 1 |
| 2 | 初三二班 | 3 | lyn | 96 | 1 |
| 2 | 初三二班 | 4 | sol | 97 | 1 |
| 2 | 初三二班 | 7 | b1 | 81 | 2 |
| 2 | 初三二班 | 8 | b2 | 82 | 2 |
| 2 | 初三二班 | 13 | c1 | 71 | 3 |
| 2 | 初三二班 | 14 | c2 | 72.5 | 3 |
| 2 | 初三二班 | 19 | lala | 51 | 0 |
| 3 | 初三三班 | 1 | brand | 97.5 | 1 |
| 3 | 初三三班 | 2 | helen | 96.5 | 1 |
| 3 | 初三三班 | 3 | lyn | 96 | 1 |
| 3 | 初三三班 | 4 | sol | 97 | 1 |
| 3 | 初三三班 | 7 | b1 | 81 | 2 |
| 3 | 初三三班 | 8 | b2 | 82 | 2 |
| 3 | 初三三班 | 13 | c1 | 71 | 3 |
| 3 | 初三三班 | 14 | c2 | 72.5 | 3 |
| 3 | 初三三班 | 19 | lala | 51 | 0 |
| 4 | 初三四班 | 1 | brand | 97.5 | 1 |
| 4 | 初三四班 | 2 | helen | 96.5 | 1 |
| 4 | 初三四班 | 3 | lyn | 96 | 1 |
| 4 | 初三四班 | 4 | sol | 97 | 1 |
| 4 | 初三四班 | 7 | b1 | 81 | 2 |
| 4 | 初三四班 | 8 | b2 | 82 | 2 |
| 4 | 初三四班 | 13 | c1 | 71 | 3 |
| 4 | 初三四班 | 14 | c2 | 72.5 | 3 |
| 4 | 初三四班 | 19 | lala | 51 | 0 |
+---------+-----------+-----------+-------------+-------+---------+
36 rows in set

這樣的數據肯定不是我們想要的，在實際應用中，表連接時要加上限制條件，才能夠篩選出我們真正需要的數據。

我們主要的連接查詢有這幾種：內連接、左(外)連接、右(外)連接，下面我們一一來看。

內連接查詢 inner join

語法格式：

1
2
3
4
5 select cname from tname1 inner join tname2 on join condition;
或者
select cname from tname1 join tname2 on join condition;
或者
select cname from tname1,tname2 [where join condition];

說明：在笛卡爾積的基礎上加上了連接條件，組合兩個表，返回符合連接條件的記錄，也就是返回兩個表的交集（陰影）部分。如果沒有加上這個連接條件，就是上面笛卡爾積的結果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14 mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand | 97.5 |
| 初三一班 | helen | 96.5 |
| 初三一班 | lyn | 96 |
| 初三一班 | sol | 97 |
| 初三二班 | b1 | 81 |
| 初三二班 | b2 | 82 |
| 初三三班 | c1 | 71 |
| 初三三班 | c2 | 72.5 |
+-----------+-------------+-------+
8 rows in set

從上面的數據可以看出，初三四班 classid = 4，因為沒有關聯的學生，所以被過濾掉了；lala 同學的classid=0，沒法關聯到具體的班級，也被過濾掉了，只取兩表都有的數據交集

1
2
3
4
5
6
7
8
9
10 mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand | 97.5 |
| 初三一班 | helen | 96.5 |
| 初三一班 | lyn | 96 |
| 初三一班 | sol | 97 |
+-----------+-------------+-------+
4 rows in set

查找1班同學的成績信息，上面語法格式的第三種，這種方式簡潔高效，直接在連接查詢的結果后面進行Where條件篩選。

左連接查詢 left join

left join on / left outer join on，語法格式：

1	`select` `cname` `from` `tname1` `left` `join` `tname2` `on` `join` `condition;`

說明： left join 是left outer join的簡寫，全稱是左外連接，外連接中的一種。左(外)連接，左表(classes)的記錄將會全部出來，而右表(students)只會顯示符合搜索條件的記錄。右表無法關聯的內容均為null。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand | 97.5 |
| 初三一班 | helen | 96.5 |
| 初三一班 | lyn | 96 |
| 初三一班 | sol | 97 |
| 初三二班 | b1 | 81 |
| 初三二班 | b2 | 82 |
| 初三三班 | c1 | 71 |
| 初三三班 | c2 | 72.5 |
| 初三四班 | NULL | NULL |
+-----------+-------------+-------+
9 rows in set

從上面結果中可以看出，初三四班無法找到對應的學生，所以后面兩個字段使用null標識。

右連接查詢 right join

right join on / right outer join on，語法格式：

1	`select` `cname` `from` `tname1` `right` `join` `tname2` `on` `join` `condition;`

說明：right join是right outer join的簡寫，全稱是右外連接，外連接中的一種。與左(外)連接相反，右(外)連接，左表(classes)只會顯示符合搜索條件的記錄，而右表(students)的記錄將會全部表示出來。左表記錄不足的地方均為NULL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand | 97.5 |
| 初三一班 | helen | 96.5 |
| 初三一班 | lyn | 96 |
| 初三一班 | sol | 97 |
| 初三二班 | b1 | 81 |
| 初三二班 | b2 | 82 |
| 初三三班 | c1 | 71 |
| 初三三班 | c2 | 72.5 |
| NULL | lala | 51 |
+-----------+-------------+-------+
9 rows in set

從上面結果中可以看出，lala同學無法找到班級，所以班級名稱字段為null。

連接查詢+聚合函數

使用連接查詢的時候，經常會配合使用聚集函數來進行數據匯總。比如在上面的數據基礎上查詢出每個班級的人數和平均分數、班級總分數。

1
2
3
4
5
6
7
8
9
10
11 mysql> select a.classname as '班級名稱',count(b.studentid) as '總人數',sum(b.score) as '總分',avg(b.score) as '平均分'
from classes a inner join students b on a.classid = b.classid
group by a.classid,a.classname;
+----------+--------+--------+-----------+
| 班級名稱 | 總人數 | 總分 | 平均分 |
+----------+--------+--------+-----------+
| 初三一班 | 4 | 387.00 | 96.750000 |
| 初三二班 | 2 | 163.00 | 81.500000 |
| 初三三班 | 2 | 143.50 | 71.750000 |
+----------+--------+--------+-----------+
3 rows in set

這邊連表查詢的同時對班級（classid，classname）做了分組，并輸出每個班級的人數、平均分、班級總分。

連接查詢附加過濾條件

使用連接查詢之后，大概率會對數據進行在過濾篩選，所以我們可以在連接查詢之后再加上where條件，比如我們根據上述的結果只取出一班的同學信息。

1
2
3
4
5
6
7
8
9
10 mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand | 97.5 |
| 初三一班 | helen | 96.5 |
| 初三一班 | lyn | 96 |
| 初三一班 | sol | 97 |
+-----------+-------------+-------+
4 rows in set

如上，只輸出一班的同學，同理，可以附件 limit 限制，order by排序等操作。

總結

1、連接查詢必然要帶上連接條件，否則會變成笛卡爾乘積數據，使用不正確的聯結條件，也將返回不正確的數據。

2、SQL規范推薦首選INNER JOIN語法。但是連接的幾種方式本身并沒有明顯的性能差距，性能的差距主要是由數據的結構、連接的條件，索引的使用等多種條件綜合決定的。

我們應該根據實際的業務場景來決定，比如上述數據場景：如果要求返回返回有學生的班級就使用 inner join；如果必須輸出所有班級則使用left join；如果必須輸出所有學生，則使用right join。

3、性能上的考慮，MySQL在運行時會根據關聯條件處理連接的表，這種處理可能是非常耗費資源的，連接的表越多，性能下降越厲害。所以要分析去除那些不必要的連接和不需要顯示的字段。

之前我的項目團隊在優化舊的業務代碼時，發現隨著業務的變更，某些數據不需要顯示，對應的某個連接也不需要了，去掉之后，性能較大提升。

以上就是MySQL 連接查詢的原理和應用的詳細內容

相關信息

MYSQL數據庫主從同步設置的實現步驟
為什么要主從同步？主從同步的原理 MYSQL數據庫進行主從同步創建兩個MYSQL數據庫初始化，安裝主機數據庫配置從機測試主從同步 ...

2022-03-01
銀河麒麟V10安裝MySQL8.0.28并實現遠程訪問
這篇文章主要介紹了銀河麒麟V10安裝MySQL8028的圖文教程，并詳細介紹了遠程訪問的實現方法，本文通過圖文命令給大家介紹的非常詳細...

2022-02-28

熱門系統總排行

裝機必備

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

jlzzjlzz亚洲乱熟在线播放