通俗來講，left join就是以左表作為主表，結果返回左表的所有記錄，右表滿足條件記錄正常顯示，滿足條件記錄使用NULL做填充，一般業務中我們需要顯示左表全部記錄時才會使用left join。另外，某些情況下MySQL優化器會將我們的left join改寫為join，什么情況下MySQL會做這樣的優化？

1.1 測試數據

root@mysql 11:24: [db1]> show create table t1\G *************************** 1. row ***************************Table: t1 Create Table: CREATE TABLE `t1` (`uid` int(11) NOT NULL AUTO_INCREMENT,`uname` varchar(10) DEFAULT NULL,`is_delete` int(11) DEFAULT '0',PRIMARY KEY (`uid`),KEY `idx_uname` (`uname`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)root@mysql 11:24: [db1]> show create table t2\G *************************** 1. row ***************************Table: t2 Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`uname` varchar(10) DEFAULT NULL,`uage` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_uname` (`uname`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)root@mysql 11:24: [db1]> select * from t1; +-----+-------+-----------+ | uid | uname | is_delete | +-----+-------+-----------+ | 1 | aa | 0 | | 2 | bb | 0 | | 3 | cc | 0 | | 4 | dd | 0 | | 5 | ee | 0 | +-----+-------+-----------+ 5 rows in set (0.00 sec)root@mysql 11:24: [db1]> select * from t2; +----+-------+------+ | id | uname | uage | +----+-------+------+ | 1 | aa | 12 | | 2 | aa | 13 | | 5 | cc | 12 | +----+-------+------+ 3 rows in set (0.00 sec)

1.2 條件放在on和where之前的區別

1、從結果顯示來看

從結果顯示來看兩者是完全不同的：

1）當把過濾條件寫在and上時，返回結果集中會顯示左表全部記錄，右表滿足條件的記錄正常顯示，不滿足條件的記錄顯示為NULL，是我們通俗理解上left join應該顯示的結果；

2）而當把過濾條件寫在where上時，雖然SQL中我們使用了left join去做表關聯，但是實際結果集中并不是我們想要的返回，結果只是返回了滿足條件的所有記錄。

root@mysql 11:24: [db1]> select * from t1 left join t2 on t1.uname=t2.uname and t2.uage>12; +-----+-------+-----------+------+-------+------+ | uid | uname | is_delete | id | uname | uage | +-----+-------+-----------+------+-------+------+ | 1 | aa | 0 | 2 | aa | 13 | | 2 | bb | 0 | NULL | NULL | NULL | | 3 | cc | 0 | NULL | NULL | NULL | | 4 | dd | 0 | NULL | NULL | NULL | | 5 | ee | 0 | NULL | NULL | NULL | +-----+-------+-----------+------+-------+------+ 5 rows in set (0.00 sec)root@mysql 11:25: [db1]> select * from t1 left join t2 on t1.uname=t2.uname where t2.uage>12; +-----+-------+-----------+------+-------+------+ | uid | uname | is_delete | id | uname | uage | +-----+-------+-----------+------+-------+------+ | 1 | aa | 0 | 2 | aa | 13 | +-----+-------+-----------+------+-------+------+ 1 row in set (0.00 sec)

2、從執行計劃來看

1）當把過濾條件寫在and上時，執行計劃沒有做過多的改寫，左表t1作為驅動表與t2進行關聯查詢；

2）當把過濾條件寫在where上時，我們發現MySQL對原SQL進行了改寫，最重要的一點是將left join改寫為join，這個動作就導致SQL在執行計劃中會優先選擇小表作為驅動表，而并不一定是左表t1作為驅動表。

root@mysql 11:26: [db1]> explain select * from t1 left join t2 on t1.uname=t2.uname and t2.uage>12; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | idx_uname | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)root@mysql 11:26: [db1]> show warnings\G *************************** 1. row ***************************Level: NoteCode: 1003 Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` left join `db1`.`t2` on(((`db1`.`t2`.`uname` = `db1`.`t1`.`uname`) and (`db1`.`t2`.`uage` > 12))) where 1 1 row in set (0.00 sec)root@mysql 11:26: [db1]> root@mysql 11:26: [db1]> root@mysql 11:26: [db1]> explain select * from t1 left join t2 on t1.uname=t2.uname where t2.uage>12; +----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+ | 1 | SIMPLE | t2 | NULL | ALL | idx_uname | NULL | NULL | NULL | 3 | 33.33 | Using where | | 1 | SIMPLE | t1 | NULL | ref | idx_uname | idx_uname | 43 | db1.t2.uname | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)root@mysql 11:26: [db1]> show warnings\G *************************** 1. row ***************************Level: NoteCode: 1003 Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` join `db1`.`t2` where ((`db1`.`t1`.`uname` = `db1`.`t2`.`uname`) and (`db1`.`t2`.`uage` > 12)) 1 row in set (0.00 sec)

1.3 left join … where … is null 的使用

基于以上的案例，我們可以得出以下結論：

1）left join會返回左表所有記錄，右表滿足過濾條件記錄正常反饋，不滿足記錄返回NULL處理，join只會返回兩表均滿足過濾條件的記錄

2）left join把過濾條件寫在on條件上時才是我們通俗理解上的left join，而left join中將表過濾條件寫在where上時，MySQL會把left join改寫為join。

3）left join關聯查詢時，表的驅動順序是確定的，左表作為驅動表與右表進行關聯查詢，但是若MySQL優化器將left join改寫為join的情況下，MySQL就會優先選擇小表作為驅動表進行關聯查詢，一定程度上提升了SQL的執行效率。

但是，對于第一/二條，將過濾條件放在where條件上的時候，MySQL優化器就一定會將left join 改寫為join嗎？left join一定會返回左表全部記錄嗎？答案顯然是“不一定的”，以下就為大家展示一個特例。

root@mysql 11:42: [db1]> select * from t1 left join t2 on t1.uname=t2.uname; +-----+-------+-----------+------+-------+------+ | uid | uname | is_delete | id | uname | uage | +-----+-------+-----------+------+-------+------+ | 1 | aa | 0 | 1 | aa | 12 | | 1 | aa | 0 | 2 | aa | 13 | | 3 | cc | 0 | 5 | cc | 12 | | 2 | bb | 0 | NULL | NULL | NULL | | 4 | dd | 0 | NULL | NULL | NULL | | 5 | ee | 0 | NULL | NULL | NULL | +-----+-------+-----------+------+-------+------+ 6 rows in set (0.00 sec)root@mysql 11:41: [db1]> explain select * from t1 left join t2 on t1.uname=t2.uname where t2.uage is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | idx_uname | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)root@mysql 11:41: [db1]> show warnings\G *************************** 1. row ***************************Level: NoteCode: 1003 Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` left join `db1`.`t2` on((`db1`.`t2`.`uname` = `db1`.`t1`.`uname`)) where isnull(`db1`.`t2`.`uage`) 1 row in set (0.00 sec)root@mysql 11:41: [db1]> select * from t1 left join t2 on t1.uname=t2.uname where t2.uage is null; +-----+-------+-----------+------+-------+------+ | uid | uname | is_delete | id | uname | uage | +-----+-------+-----------+------+-------+------+ | 2 | bb | 0 | NULL | NULL | NULL | | 4 | dd | 0 | NULL | NULL | NULL | | 5 | ee | 0 | NULL | NULL | NULL | +-----+-------+-----------+------+-------+------+ 3 rows in set (0.00 sec)

可以看到，我們使用left join，但是where 過濾條件是右表某些字段is null的查詢時。首先從執行計劃來看，MySQL優化器并沒有將left join改寫為join；然后從結果返回來看，可以看到該SQL僅僅返回了滿足過濾條件的記錄，并沒有返回左表全部記錄。

對于left join，過濾條件是右表某字段is null的情況是一個特例，而且這種寫法經常被DBA同學用來做業務上一些not in/not exists的改寫優化。

1.4 結論

1）left join會返回左表所有記錄，右表滿足過濾條件記錄正常反饋，不滿足記錄返回NULL處理，join只會返回兩表均滿足過濾條件的記錄

2）left join把過濾條件寫在on條件上時才是我們通俗理解上的left join，而left join中將表過濾條件寫在where上時，MySQL會把left join改寫為join。

3）left join且where過濾條件為右表某字段is null時屬于一個特例，該情況下MySQL不會將left join改寫為join，從而其表關聯查詢的順序也只能是左表作為驅動表與右表進行關聯查詢。

4）left join關聯查詢時，表的驅動順序是確定的，左表作為驅動表與右表進行關聯查詢，但是若MySQL優化器將left join改寫為join的情況下，MySQL就會優先選擇小表作為驅動表進行關聯查詢，一定程度上提升了SQL的執行效率。

總結

以上是生活随笔為你收集整理的正确理解left join的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。