理论 MYSQL的LEFT JOIN 有时候会变的不一样
如果LFET JOIN B WHERE 条件部分有B的条件,那么它跟主表不再是LEFT
JOIN ,而是变成了 INNER JOIN!
我们创建两个父子表
mysql> show create table children;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| children | CREATE TABLE `children` (`ID` bigint unsigned NOT NULL AUTO_INCREMENT,`NAME` varchar(45) DEFAULT NULL,`SEX` char(1) DEFAULT NULL,`AGE` tinyint DEFAULT NULL,`FATHER_ID` bigint DEFAULT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='孩子' |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> show create table FATHER;+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| FATHER | CREATE TABLE `FATHER` (`ID` bigint unsigned NOT NULL AUTO_INCREMENT,`NAME` varchar(45) DEFAULT NULL,`MONY` decimal(10,0) DEFAULT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='父亲' |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
生成必要的数据
mysql> select * from FATHER;+----+--------+------+| ID | NAME | MONY |+----+--------+------+| 1 | 王五 | 3000 || 2 | 张三 | 3500 || 3 | 李四 | 5000 || 4 | 刘二 | 8000 || 5 | 陈七 | 5500 || 6 | 杨八 | 7500 |+----+--------+------+6 rows in set (0.00 sec)复制
mysql> select * from children;+----+-----------+------+------+-----------+| ID | NAME | SEX | AGE | FATHER_ID |+----+-----------+------+------+-----------+| 1 | 王八 | 男 | 38 | 1 || 2 | 王高 | 男 | 28 | 1 || 3 | 王艳 | 女 | 18 | 1 || 4 | 张雪 | 女 | 33 | 2 || 5 | 张亮 | 男 | 25 | 2 || 6 | 李贵 | 男 | 27 | 3 || 7 | 李丽 | 女 | 25 | 3 || 8 | 杨莉 | 女 | 23 | 6 || 9 | 杨雪 | 女 | 22 | 6 || 10 | 杨七郎 | 男 | 35 | 6 |+----+-----------+------+------+-----------+10 rows in set (0.00 sec)复制
实验一 没有条件的时候
mysql> SELECT A.NAME,A.MONY,B.NAME,B.AGE,B.SEX-> FROM books.FATHER A-> LEFT JOIN books.children B ON A.ID=B.FATHER_ID-> ORDER BY A.ID ASC;+--------+------+-----------+------+------+| NAME | MONY | NAME | AGE | SEX |+--------+------+-----------+------+------+| 王五 | 3000 | 王艳 | 18 | 女 || 王五 | 3000 | 王高 | 28 | 男 || 王五 | 3000 | 王八 | 38 | 男 || 张三 | 3500 | 张亮 | 25 | 男 || 张三 | 3500 | 张雪 | 33 | 女 || 李四 | 5000 | 李丽 | 25 | 女 || 李四 | 5000 | 李贵 | 27 | 男 || 刘二 | 8000 | NULL | NULL | NULL || 陈七 | 5500 | NULL | NULL | NULL || 杨八 | 7500 | 杨七郎 | 35 | 男 || 杨八 | 7500 | 杨雪 | 22 | 女 || 杨八 | 7500 | 杨莉 | 23 | 女 |+--------+------+-----------+------+------+12 rows in set (0.00 sec)复制
认为非常正常,没有孩子的要展现出来的!
执行计划也很正常
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| 1 | SIMPLE | A | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary; Using filesort || 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+复制
实验二 带条件的
mysql> SELECT A.NAME,A.MONY,B.NAME,B.AGE,B.SEX-> FROM books.FATHER A-> LEFT JOIN books.children B ON A.ID=B.FATHER_ID-> WHERE B.SEX='男'-> ORDER BY A.ID ASC;+--------+------+-----------+------+------+| NAME | MONY | NAME | AGE | SEX |+--------+------+-----------+------+------+| 王五 | 3000 | 王八 | 38 | 男 || 王五 | 3000 | 王高 | 28 | 男 || 张三 | 3500 | 张亮 | 25 | 男 || 李四 | 5000 | 李贵 | 27 | 男 || 杨八 | 7500 | 杨七郎 | 35 | 男 |+--------+------+-----------+------+------+复制
主表的被屏蔽掉了,而执行计划是B表为主表,驱动表
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------+| 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where; Using temporary; Using filesort || 1 | SIMPLE | A | NULL | eq_ref | PRIMARY | PRIMARY | 8 | books.B.FATHER_ID | 1 | 100.00 | Using where |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------+复制
查看SQL改写
mysql> show warnings-> ;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `books`.`A`.`NAME` AS `NAME`,`books`.`A`.`MONY` AS `MONY`,`books`.`B`.`NAME` AS `NAME`,`books`.`B`.`AGE` AS `AGE`,`books`.`B`.`SEX` AS `SEX` from `books`.`FATHER` `A` join `books`.`children` `B` where ((`books`.`B`.`SEX` = '男') and (`books`.`A`.`ID` = `books`.`B`.`FATHER_ID`)) order by `books`.`A`.`ID` |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
被改写成了 JOIN
select`books`.`A`.`NAME` AS `NAME`,`books`.`A`.`MONY` AS `MONY`,`books`.`B`.`NAME` AS `NAME`,`books`.`B`.`AGE` AS `AGE`,`books`.`B`.`SEX` AS `SEX`from`books`.`FATHER` `A`join `books`.`children` `B`where((`books`.`B`.`SEX` = '男')and (`books`.`A`.`ID` = `books`.`B`.`FATHER_ID`))order by`books`.`A`.`ID`复制
如果不是你希望的,还是希望要回LEFT JOIN 该这样写 把条件写在ON 后面
mysql> SELECT A.NAME,A.MONY,B.NAME,B.AGE,B.SEX-> FROM books.FATHER A-> LEFT JOIN books.children B ON A.ID=B.FATHER_ID AND B.SEX='男'-> ORDER BY A.ID ASC;+--------+------+-----------+------+------+| NAME | MONY | NAME | AGE | SEX |+--------+------+-----------+------+------+| 王五 | 3000 | 王高 | 28 | 男 || 王五 | 3000 | 王八 | 38 | 男 || 张三 | 3500 | 张亮 | 25 | 男 || 李四 | 5000 | 李贵 | 27 | 男 || 刘二 | 8000 | NULL | NULL | NULL || 陈七 | 5500 | NULL | NULL | NULL || 杨八 | 7500 | 杨七郎 | 35 | 男 |+--------+------+-----------+------+------+7 rows in set (0.00 sec)复制
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| 1 | SIMPLE | A | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary; Using filesort || 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+复制
select`books`.`A`.`NAME` AS `NAME`,`books`.`A`.`MONY` AS `MONY`,`books`.`B`.`NAME` AS `NAME`,`books`.`B`.`AGE` AS `AGE`,`books`.`B`.`SEX` AS `SEX`from`books`.`FATHER` `A`left join `books`.`children` `B` on(((`books`.`B`.`SEX` = '男')and (`books`.`A`.`ID` = `books`.`B`.`FATHER_ID`)))wheretrueorder by`books`.`A`.`ID`复制
MYSQL开发
文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
