1. 前言

對(duì)于連接操作，驅(qū)動(dòng)表和被驅(qū)動(dòng)表的關(guān)聯(lián)條件我們放在on后面，如果額外增加對(duì)驅(qū)動(dòng)表和被驅(qū)動(dòng)表的過濾條件，放到on或者where后面都不會(huì)報(bào)錯(cuò)，但是得到的結(jié)果集卻是不一樣的？？？

1.1 mysql連接的原理

眾所周知，mysql是基于嵌套循環(huán)連接（Nested-Loop Join，暫不考慮優(yōu)化算法）算法來進(jìn)行表之間的連接操作的，大致過程如下：

• 選取驅(qū)動(dòng)表，使用與驅(qū)動(dòng)表相關(guān)的過濾條件執(zhí)行對(duì)驅(qū)動(dòng)表的單表查詢；
• 對(duì)于查詢到的驅(qū)動(dòng)表中的每一條紀(jì)錄，分別到被驅(qū)動(dòng)表中查找匹配的紀(jì)錄。

偽代碼如下：

for each row in t1 {      // 遍歷滿足對(duì)t1單表查詢結(jié)果集中的每一條紀(jì)錄
    for each row in t2 {  // 對(duì)于某條t1紀(jì)錄，遍歷滿足對(duì)t2單表查詢結(jié)果集中的每一條紀(jì)錄
        if row satisfies join conditions, send to client
    }
}

1.2 show warnings命令

我們寫的sql語句，在經(jīng)過優(yōu)化器優(yōu)化后才會(huì)交給執(zhí)行器執(zhí)行，而show warnings命令則可以幫助我們獲得優(yōu)化器優(yōu)化后的sql。

2. 準(zhǔn)備工作

表結(jié)構(gòu)如下：

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  `stu_name` varchar(30) NOT NULL DEFAULT '',
  `stu_sex` varchar(10) NOT NULL DEFAULT '',
  `stu_age` int(10) NOT NULL DEFAULT '0',
  `stu_dept` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uq_stu_code` (`stu_code`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4

CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cou_code` varchar(20) NOT NULL DEFAULT '',
  `cou_name` varchar(50) NOT NULL DEFAULT '',
  `cou_score` int(10) NOT NULL DEFAULT '0',
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_stu_code_cou_code` (`stu_code`,`cou_code`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

表數(shù)據(jù)如下： 

3. inner join內(nèi)連接on、where的區(qū)別

sql如下：

select * from student
inner join course on student.stu_code = course.stu_code
and student.stu_code >= 3 and course.cou_score >= 80;

執(zhí)行explain+sql命令： 

執(zhí)行show warnings命令： 

分析：從show warnings分析來看，對(duì)于inner join連接，經(jīng)過優(yōu)化器優(yōu)化后，on連接條件會(huì)轉(zhuǎn)化為where！也就是說內(nèi)連接中的where和on是等價(jià)的。

4. left join左連接on、where的區(qū)別

4.1 where驅(qū)動(dòng)表過濾條件

sql如下：

select * from student
left join course on student.stu_code = course.stu_code
where student.stu_code >= 3;

執(zhí)行explain+sql命令： 

執(zhí)行show warnings命令： 

結(jié)果集： 

分析：從explain分析看出，student作為驅(qū)動(dòng)表，把student.stu_code >= 3作為過濾條件進(jìn)行全表掃描，然后把查詢到的每條紀(jì)錄的student.stu_code（也就是on條件里面的）分別作為過濾條件讓被驅(qū)動(dòng)表course做單表查詢。

4.2 on驅(qū)動(dòng)表過濾條件

sql如下：

select * from student
left join course on student.stu_code = course.stu_code 
and student.stu_code >= 3;

執(zhí)行explain+sql命令： 

執(zhí)行show warnings命令： 

結(jié)果集： 

從結(jié)果集來看，student.stu_code >= 3并未生效，為什么？

分析：從explain分析看出，student作為驅(qū)動(dòng)表，做全表掃描，然后把查詢到的每條記錄的student.stu_code和student.stu_code >= 3（也就是on條件里面的）分別做為過濾條件讓被驅(qū)動(dòng)表做單表查詢；此時(shí)student.stu_code >= 3對(duì)驅(qū)動(dòng)表是不過濾的，僅在連接被驅(qū)動(dòng)表時(shí)生效，查詢不到符合紀(jì)錄而返回NULL！

4.3 on被驅(qū)動(dòng)表過濾條件

sql如下：

select * from student
left join course on student.stu_code = course.stu_code 
and course.cou_score >= 80;

執(zhí)行explain+sql命令： 

執(zhí)行show warnings命令： 

結(jié)果集： 

分析：從explain分析看出，student作為驅(qū)動(dòng)表，做全表掃描，然后把查詢到的每條記錄的student.stu_code和course.cou_score >= 80（也就是on條件里面的）分別做為過濾條件讓被驅(qū)動(dòng)表做單表查詢；

4.4 where被驅(qū)動(dòng)表過濾條件

sql如下： 

執(zhí)行explain+sql命令： 

執(zhí)行show warnings命令： 

結(jié)果集： 

從show warnings分析來看？left join連接變成了inner join連接？

分析：從show warnings分析看出，如果被驅(qū)動(dòng)表有過濾條件在where，那么left join會(huì)被失效，被優(yōu)化成inner join連接。所以被驅(qū)動(dòng)表的過濾條件應(yīng)該放在on而不是where。

5. 總結(jié)

其實(shí)，在內(nèi)連接的基礎(chǔ)上引入外連接的概念，就是為了解決驅(qū)動(dòng)表中的紀(jì)錄即使沒有在被驅(qū)動(dòng)表中找到匹配的紀(jì)錄，仍要加入結(jié)果集的問題。所以對(duì)于外連接（外連接包括：左連接、右連接），被驅(qū)動(dòng)表的過濾條件我們應(yīng)該放在on！

到此這篇關(guān)于Mysql表連接的執(zhí)行流程詳解的文章就介紹到這了,更多相關(guān)Mysql表連接 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論