概述
MySQL最強大的功能之一就是能在資料檢索的執行中連接配接(join)表。大部分的單表資料查詢并不能滿足我們的需求,這時候我們就需要連接配接一個或者多個表,并通過一些條件過濾篩選出我們需要的資料。
了解MySQL連接配接查詢之前我們先來了解下笛卡爾積的原理。
資料準備
依舊使用上節的表資料(包含classes 班級表和students 學生表):
1 mysql> select * from classes;
2 +---------+-----------+
3 | classid | classname |
4 +---------+-----------+
5 | 1 | 初三一班 |
6 | 2 | 初三二班 |
7 | 3 | 初三三班 |
8 | 4 | 初三四班 |
9 +---------+-----------+
10 4 rows in set
11
12 mysql> select * from students;
13 +-----------+-------------+-------+---------+
14 | studentid | studentname | score | classid |
15 +-----------+-------------+-------+---------+
16 | 1 | brand | 97.5 | 1 |
17 | 2 | helen | 96.5 | 1 |
18 | 3 | lyn | 96 | 1 |
19 | 4 | sol | 97 | 1 |
20 | 7 | b1 | 81 | 2 |
21 | 8 | b2 | 82 | 2 |
22 | 13 | c1 | 71 | 3 |
23 | 14 | c2 | 72.5 | 3 |
24 | 19 | lala | 51 | 0 |
25 +-----------+-------------+-------+---------+
26 9 rows in set
笛卡爾積
笛卡爾積:也就是笛卡爾乘積,假設兩個集合A和B,笛卡爾積表示A集合中的元素和B集合中的元素任意互相關聯産生的所有可能的結果。
比如A中有m個元素,B中有n個元素,A、B笛卡爾積産生的結果有m*n個結果,相當于循環周遊兩個集合中的元素,任意組合。
笛卡爾積在SQL中的實作方式既是交叉連接配接(Cross Join)。所有連接配接方式都會先生成臨時笛卡爾積表,笛卡爾積是關系代數裡的一個概念,表示兩個表中的每一行資料任意組合。
是以上面的表就是 4(班級表)* 9(學生表) = 36條資料;
笛卡爾積文法格式:
1 select cname1,cname2,... from tname1,tname2,...;
2 or
3 select cname from tname1 join tname2 [join tname...];
圖例表示:
上述兩個表實際執行結果如下:
1 mysql> select * from classes a,students b order by a.classid,b.studentid;
2 +---------+-----------+-----------+-------------+-------+---------+
3 | classid | classname | studentid | studentname | score | classid |
4 +---------+-----------+-----------+-------------+-------+---------+
5 | 1 | 初三一班 | 1 | brand | 97.5 | 1 |
6 | 1 | 初三一班 | 2 | helen | 96.5 | 1 |
7 | 1 | 初三一班 | 3 | lyn | 96 | 1 |
8 | 1 | 初三一班 | 4 | sol | 97 | 1 |
9 | 1 | 初三一班 | 7 | b1 | 81 | 2 |
10 | 1 | 初三一班 | 8 | b2 | 82 | 2 |
11 | 1 | 初三一班 | 13 | c1 | 71 | 3 |
12 | 1 | 初三一班 | 14 | c2 | 72.5 | 3 |
13 | 1 | 初三一班 | 19 | lala | 51 | 0 |
14 | 2 | 初三二班 | 1 | brand | 97.5 | 1 |
15 | 2 | 初三二班 | 2 | helen | 96.5 | 1 |
16 | 2 | 初三二班 | 3 | lyn | 96 | 1 |
17 | 2 | 初三二班 | 4 | sol | 97 | 1 |
18 | 2 | 初三二班 | 7 | b1 | 81 | 2 |
19 | 2 | 初三二班 | 8 | b2 | 82 | 2 |
20 | 2 | 初三二班 | 13 | c1 | 71 | 3 |
21 | 2 | 初三二班 | 14 | c2 | 72.5 | 3 |
22 | 2 | 初三二班 | 19 | lala | 51 | 0 |
23 | 3 | 初三三班 | 1 | brand | 97.5 | 1 |
24 | 3 | 初三三班 | 2 | helen | 96.5 | 1 |
25 | 3 | 初三三班 | 3 | lyn | 96 | 1 |
26 | 3 | 初三三班 | 4 | sol | 97 | 1 |
27 | 3 | 初三三班 | 7 | b1 | 81 | 2 |
28 | 3 | 初三三班 | 8 | b2 | 82 | 2 |
29 | 3 | 初三三班 | 13 | c1 | 71 | 3 |
30 | 3 | 初三三班 | 14 | c2 | 72.5 | 3 |
31 | 3 | 初三三班 | 19 | lala | 51 | 0 |
32 | 4 | 初三四班 | 1 | brand | 97.5 | 1 |
33 | 4 | 初三四班 | 2 | helen | 96.5 | 1 |
34 | 4 | 初三四班 | 3 | lyn | 96 | 1 |
35 | 4 | 初三四班 | 4 | sol | 97 | 1 |
36 | 4 | 初三四班 | 7 | b1 | 81 | 2 |
37 | 4 | 初三四班 | 8 | b2 | 82 | 2 |
38 | 4 | 初三四班 | 13 | c1 | 71 | 3 |
39 | 4 | 初三四班 | 14 | c2 | 72.5 | 3 |
40 | 4 | 初三四班 | 19 | lala | 51 | 0 |
41 +---------+-----------+-----------+-------------+-------+---------+
42 36 rows in set
這樣的資料肯定不是我們想要的,在實際應用中,表連接配接時要加上限制條件,才能夠篩選出我們真正需要的資料。
我們主要的連接配接查詢有這幾種:内連接配接、左(外)連接配接、右(外)連接配接,下面我們一 一來看。
内連接配接查詢 inner join
文法格式:
1 select cname from tname1 inner join tname2 on join condition;
2 或者
3 select cname from tname1 join tname2 on join condition;
4 或者
5 select cname from tname1,tname2 [where join condition];
說明:在笛卡爾積的基礎上加上了連接配接條件,組合兩個表,傳回符合連接配接條件的記錄,也就是傳回兩個表的交集(陰影)部分。如果沒有加上這個連接配接條件,就是上面笛卡爾積的結果。
1 mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid;
2 +-----------+-------------+-------+
3 | classname | studentname | score |
4 +-----------+-------------+-------+
5 | 初三一班 | brand | 97.5 |
6 | 初三一班 | helen | 96.5 |
7 | 初三一班 | lyn | 96 |
8 | 初三一班 | sol | 97 |
9 | 初三二班 | b1 | 81 |
10 | 初三二班 | b2 | 82 |
11 | 初三三班 | c1 | 71 |
12 | 初三三班 | c2 | 72.5 |
13 +-----------+-------------+-------+
14 8 rows in set
從上面的資料可以看出 ,初三四班 classid = 4,因為沒有關聯的學生,是以被過濾掉了;lala 同學的classid=0,沒法關聯到具體的班級,也被過濾掉了,隻取兩表都有的資料交集
1 mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1;
2 +-----------+-------------+-------+
3 | classname | studentname | score |
4 +-----------+-------------+-------+
5 | 初三一班 | brand | 97.5 |
6 | 初三一班 | helen | 96.5 |
7 | 初三一班 | lyn | 96 |
8 | 初三一班 | sol | 97 |
9 +-----------+-------------+-------+
10 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 mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid;
2 +-----------+-------------+-------+
3 | classname | studentname | score |
4 +-----------+-------------+-------+
5 | 初三一班 | brand | 97.5 |
6 | 初三一班 | helen | 96.5 |
7 | 初三一班 | lyn | 96 |
8 | 初三一班 | sol | 97 |
9 | 初三二班 | b1 | 81 |
10 | 初三二班 | b2 | 82 |
11 | 初三三班 | c1 | 71 |
12 | 初三三班 | c2 | 72.5 |
13 | 初三四班 | NULL | NULL |
14 +-----------+-------------+-------+
15 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 mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid;
2 +-----------+-------------+-------+
3 | classname | studentname | score |
4 +-----------+-------------+-------+
5 | 初三一班 | brand | 97.5 |
6 | 初三一班 | helen | 96.5 |
7 | 初三一班 | lyn | 96 |
8 | 初三一班 | sol | 97 |
9 | 初三二班 | b1 | 81 |
10 | 初三二班 | b2 | 82 |
11 | 初三三班 | c1 | 71 |
12 | 初三三班 | c2 | 72.5 |
13 | NULL | lala | 51 |
14 +-----------+-------------+-------+
15 9 rows in set
從上面結果中可以看出,lala同學無法找到班級,是以班級名稱字段為null。
連接配接查詢+聚合函數
使用連接配接查詢的時候,經常會配合使用聚集函數來進行資料彙總。比如在上面的資料基礎上查詢出每個班級的人數和平均分數、班級總分數。
1 mysql> select a.classname as '班級名稱',count(b.studentid) as '總人數',sum(b.score) as '總分',avg(b.score) as '平均分'
2 from classes a inner join students b on a.classid = b.classid
3 group by a.classid,a.classname;
4 +----------+--------+--------+-----------+
5 | 班級名稱 | 總人數 | 總分 | 平均分 |
6 +----------+--------+--------+-----------+
7 | 初三一班 | 4 | 387.00 | 96.750000 |
8 | 初三二班 | 2 | 163.00 | 81.500000 |
9 | 初三三班 | 2 | 143.50 | 71.750000 |
10 +----------+--------+--------+-----------+
11 3 rows in set
這邊連表查詢的同時對班級(classid,classname)做了分組,并輸出每個班級的人數、平均分、班級總分。
連接配接查詢附加過濾條件
使用連接配接查詢之後,大機率會對資料進行在過濾篩選,是以我們可以在連接配接查詢之後再加上where條件,比如我們根據上述的結果隻取出一班的同學資訊。
1 mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1;
2 +-----------+-------------+-------+
3 | classname | studentname | score |
4 +-----------+-------------+-------+
5 | 初三一班 | brand | 97.5 |
6 | 初三一班 | helen | 96.5 |
7 | 初三一班 | lyn | 96 |
8 | 初三一班 | sol | 97 |
9 +-----------+-------------+-------+
10 4 rows in set
如上,隻輸出一班的同學,同理,可以附件 limit 限制,order by排序等操作。
總結
1、連接配接查詢必然要帶上連接配接條件,否則會變成笛卡爾乘積資料,使用不正确的聯結條件,也将傳回不正确的資料。
2、SQL規範推薦首選INNER JOIN文法。但是連接配接的幾種方式本身并沒有明顯的性能差距,性能的差距主要是由資料的結構、連接配接的條件,索引的使用等多種條件綜合決定的。
我們應該根據實際的業務場景來決定,比如上述資料場景:如果要求傳回傳回有學生的班級就使用 inner join;如果必須輸出所有班級則使用left join;如果必須輸出所有學生,則使用right join。
3、性能上的考慮,MySQL在運作時會根據關聯條件處理連接配接的表,這種處理可能是非常耗費資源的,連接配接的表越多,性能下降越厲害。是以要分析去除那些不必要的連接配接和不需要顯示的字段。
之前我的項目團隊在優化舊的業務代碼時,發現随着業務的變更,某些資料不需要顯示,對應的某個連接配接也不需要了,去掉之後,性能較大提升。
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司,特别制作了這個專輯——這一次整體放出。
大緻内容包括了: Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧!
歡迎大家關注公衆号【Java爛豬皮】,回複【666】,擷取以上最新Java後端架構VIP學習資料以及視訊學習教程,然後一起學習,一文在手,面試我有。
每一個專欄都是大家非常關心,和非常有價值的話題,如果我的文章對你有所幫助,還請幫忙點贊、好評、轉發一下,你的支援會激勵我輸出更高品質的文章,非常感謝!