概述

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

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

數(shù)據(jù)準(zhǔn)備

依舊使用上節(jié)的表數(shù)據(jù)（包含classes 班級(jí)表和students 學(xué)生表）：

mysql> select * from classes;+---------+-----------+| classid | classname |+---------+-----------+| 1 | 初三一班 || 2 | 初三二班 || 3 | 初三三班 || 4 | 初三四班 |+---------+-----------+4 rows in setmysql> 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

笛卡爾積

笛卡爾積：也就是笛卡爾乘積，假設(shè)兩個(gè)集合A和B，笛卡爾積表示A集合中的元素和B集合中的元素任意相互關(guān)聯(lián)產(chǎn)生的所有可能的結(jié)果。

比如A中有m個(gè)元素，B中有n個(gè)元素，A、B笛卡爾積產(chǎn)生的結(jié)果有m*n個(gè)結(jié)果，相當(dāng)于循環(huán)遍歷兩個(gè)集合中的元素，任意組合。

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

所以上面的表就是 4（班級(jí)表）* 9（學(xué)生表） = 36條數(shù)據(jù)；

笛卡爾積語(yǔ)法格式：

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

圖例表示：

上述兩個(gè)表實(shí)際執(zhí)行結(jié)果如下：

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

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

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

內(nèi)連接查詢 inner join

語(yǔ)法格式：

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];

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

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

從上面的數(shù)據(jù)可以看出 ，初三四班 classid = 4，因?yàn)闆](méi)有關(guān)聯(lián)的學(xué)生，所以被過(guò)濾掉了；lala 同學(xué)的classid=0，沒(méi)法關(guān)聯(lián)到具體的班級(jí)，也被過(guò)濾掉了，只取兩表都有的數(shù)據(jù)交集

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班同學(xué)的成績(jī)信息，上面語(yǔ)法格式的第三種，這種方式簡(jiǎn)潔高效，直接在連接查詢的結(jié)果后面進(jìn)行Where條件篩選。

左連接查詢 left join

left join on / left outer join on，語(yǔ)法格式：

select cname from tname1 left join tname2 on join condition;

說(shuō)明： left join 是left outer join的簡(jiǎn)寫(xiě)，全稱(chēng)是左外連接，外連接中的一種。 左(外)連接，左表(classes)的記錄將會(huì)全部出來(lái)，而右表(students)只會(huì)顯示符合搜索條件的記錄。右表無(wú)法關(guān)聯(lián)的內(nèi)容均為null。

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

從上面結(jié)果中可以看出，初三四班無(wú)法找到對(duì)應(yīng)的學(xué)生，所以后面兩個(gè)字段使用null標(biāo)識(shí)。

右連接查詢 right join

right join on / right outer join on，語(yǔ)法格式：

select cname from tname1 right join tname2 on join condition;

說(shuō)明：right join是right outer join的簡(jiǎn)寫(xiě)，全稱(chēng)是右外連接，外連接中的一種。與左(外)連接相反，右(外)連接，左表(classes)只會(huì)顯示符合搜索條件的記錄，而右表(students)的記錄將會(huì)全部表示出來(lái)。左表記錄不足的地方均為NULL。

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

從上面結(jié)果中可以看出，lala同學(xué)無(wú)法找到班級(jí)，所以班級(jí)名稱(chēng)字段為null。

連接查詢+聚合函數(shù)

使用連接查詢的時(shí)候，經(jīng)常會(huì)配合使用聚集函數(shù)來(lái)進(jìn)行數(shù)據(jù)匯總。比如在上面的數(shù)據(jù)基礎(chǔ)上查詢出每個(gè)班級(jí)的人數(shù)和平均分?jǐn)?shù)、班級(jí)總分?jǐn)?shù)。

mysql> select a.classname as ’班級(jí)名稱(chēng)’,count(b.studentid) as ’總?cè)藬?shù)’,sum(b.score) as ’總分’,avg(b.score) as ’平均分’from classes a inner join students b on a.classid = b.classidgroup by a.classid,a.classname;+----------+--------+--------+-----------+| 班級(jí)名稱(chēng) | 總?cè)藬?shù) | 總分 | 平均分 |+----------+--------+--------+-----------+| 初三一班 | 4 | 387.00 | 96.750000 || 初三二班 | 2 | 163.00 | 81.500000 || 初三三班 | 2 | 143.50 | 71.750000 |+----------+--------+--------+-----------+3 rows in set

這邊連表查詢的同時(shí)對(duì)班級(jí)（classid，classname）做了分組，并輸出每個(gè)班級(jí)的人數(shù)、平均分、班級(jí)總分。

連接查詢附加過(guò)濾條件

使用連接查詢之后，大概率會(huì)對(duì)數(shù)據(jù)進(jìn)行在過(guò)濾篩選，所以我們可以在連接查詢之后再加上where條件，比如我們根據(jù)上述的結(jié)果只取出一班的同學(xué)信息。

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

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

總結(jié)

1、連接查詢必然要帶上連接條件，否則會(huì)變成笛卡爾乘積數(shù)據(jù)，使用不正確的聯(lián)結(jié)條件，也將返回不正確的數(shù)據(jù)。

2、SQL規(guī)范推薦首選INNER JOIN語(yǔ)法。但是連接的幾種方式本身并沒(méi)有明顯的性能差距，性能的差距主要是由數(shù)據(jù)的結(jié)構(gòu)、連接的條件，索引的使用等多種條件綜合決定的。

我們應(yīng)該根據(jù)實(shí)際的業(yè)務(wù)場(chǎng)景來(lái)決定，比如上述數(shù)據(jù)場(chǎng)景：如果要求返回返回有學(xué)生的班級(jí)就使用 inner join；如果必須輸出所有班級(jí)則使用left join；如果必須輸出所有學(xué)生，則使用right join。

3、性能上的考慮，MySQL在運(yùn)行時(shí)會(huì)根據(jù)關(guān)聯(lián)條件處理連接的表，這種處理可能是非常耗費(fèi)資源的，連接的表越多，性能下降越厲害。所以要分析去除那些不必要的連接和不需要顯示的字段。

之前我的項(xiàng)目團(tuán)隊(duì)在優(yōu)化舊的業(yè)務(wù)代碼時(shí)，發(fā)現(xiàn)隨著業(yè)務(wù)的變更，某些數(shù)據(jù)不需要顯示，對(duì)應(yīng)的某個(gè)連接也不需要了，去掉之后，性能較大提升。

以上就是MySQL 連接查詢的原理和應(yīng)用的詳細(xì)內(nèi)容，更多關(guān)于MySQL 連接查詢的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！