MySQL中多个left join on关联条件的顺序说明

时间:2022-11-19 p7+ 人气:0

MySQL多个left join on关联条件顺序

注意：下面的案例特别重要！请重视！SQL有点长，但确实是干货！

结论

如果存在多个left join on，请注意on后面的条件与哪个表关联。这一条统计的SQL很重要！例如表A，B，C，A left join B on A.x = B.x left join C on A.x = C.x，B和C的都要和A建立关联，B和C之间是没有任何数据上的关系。

但是如果把A.x = C.x改成B.x = C.x，那么B和C的表数据先建立关联并过滤数据，再与A表数据进行关联，这样可能会出现数据丢失！

案例

有一张分数表，表字段有日期、姓名、语文得分和数学得分等，请统计每个日期中，语文最高得分的姓名和分数，数学最低得分的姓名和分数。

思路：过滤出所有日期 left join 筛选语文 on … left join 数学得分 on …

正确的SQL：

SELECT
	* 
FROM
	( SELECT report_date reportDate FROM tb_more_left_join mlj GROUP BY mlj.report_date ) mix
	LEFT JOIN (
SELECT
	mlj.report_date maxReportDate,
	GROUP_CONCAT( mlj.user_name ) maxUserNames,
	a.maxScore 
FROM
	tb_more_left_join mlj
	LEFT JOIN ( SELECT report_date, MAX( chinese_score ) maxScore FROM tb_more_left_join mlj GROUP BY mlj.report_date ORDER BY NULL ) a ON mlj.report_date = a.report_date 
WHERE
	mlj.chinese_score = a.maxScore 
GROUP BY
	mlj.report_date 
	) mx ON mix.reportDate = mx.maxReportDate
	LEFT JOIN (
SELECT
	mlj.report_date minReportDate,
	GROUP_CONCAT( mlj.user_name ) minUserNames,
	a.minScore 
FROM
	tb_more_left_join mlj
	LEFT JOIN ( SELECT report_date, MIN( math_score ) minScore FROM tb_more_left_join mlj GROUP BY mlj.report_date ORDER BY NULL ) a ON mlj.report_date = a.report_date 
WHERE
	mlj.math_score = a.minScore 
GROUP BY
	mlj.report_date 
	) mn ON mix.reportDate = mn.minReportDate

正确结果：

错误的SQL：

把正确SQL中最后一个on条件改为mx.maxReportDate = mn.minReportDate，注意，是把mix.reportDate改为了mx.maxReportDate。

错误结果：

错误原因：

查询语文最高成绩时，没有查到2019-12-01的数据
查询数学最低成绩时，使用on与语文最高成绩关联，因为没有查到语文最高成绩的日期，所以两个表关联时，数学最低成绩即使有数据，也会因为语文最高成绩无数据而被忽略。

SQL数据

CREATE TABLE `tb_more_left_join`  (
  `id` int(11) NOT NULL,
  `report_date` date NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `math_score` int(255) NULL DEFAULT NULL,
  `chinese_score` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_more_left_join` VALUES (1, '2019-12-01', '盲僧', 70, NULL);
INSERT INTO `tb_more_left_join` VALUES (2, '2019-12-01', '薇恩', 100, NULL);
INSERT INTO `tb_more_left_join` VALUES (3, '2019-12-02', '赵信', 30, 60);
INSERT INTO `tb_more_left_join` VALUES (4, '2019-12-02', '琴女', NULL, 100);
INSERT INTO `tb_more_left_join` VALUES (5, '2019-12-03', '蛮王', 50, 100);
INSERT INTO `tb_more_left_join` VALUES (6, '2019-12-03', '艾希', 100, 100);
INSERT INTO `tb_more_left_join` VALUES (7, '2019-12-03', '亚索', 60, 90);

使用left join的on后查询碰到的大坑

很多时候我们在使用 LEFT JOIN ...... ON .... 时，除了连接两个表的字段条件外，我们往往还需要一些等值或者范围等等类似的数据筛选条件。

那么对于初学者，往往会犯一个错误，就是想当然地认为， ON 后面的条件是逐一执行的，因为没有了解清楚 ON 后面接条件的规则。

是个什么样的场景？

看实例讲解：

userinfo 表：

（找兼职的人员名单信息表）

jobinfo表：

（兼职工作信息及职业要求表）

业务需求：

根据职业要求给找兼职的人员匹配上目前可以做的兼职，输出数据条。

例如，李三是一个程序员，他迫于经济压力，不得不向社会低头，想找一些自己能做的兼职。

使用 WHERE

如果我们不用 left join ...... on ... , 仅仅使用 where，那么简单写下sql是：

SELECT * 
FROM userinfo AS u ,jobinfo AS j 
WHERE u.userProfession=j.professionRequire
AND j.professionRequire='程序员'

查询出来的结果如下：

是我们需要的结果，可以看的，程序员李三能做的兼职有，送外卖或者当保安。

使用 LEFT JOIN ...... ON ......

初学者（罪过）写的SQL ：

想当然地把筛选条件职业要求为 ‘ 程序员’ 直接 拼接在 ON 后面

SELECT * 
FROM userinfo AS u 
LEFT JOIN   
jobinfo AS j 
ON u.userProfession=j.professionRequire
AND j.professionRequire='程序员'

这样地拼接筛选条件其实是达不到所想要的效果的，先来看看这样的执行结果：

可以看到查询出来很多我们不想要的数据，为什么会这样？

原因

因为如果直接把关联表的筛选条件拼接在 ON 后，执行的顺序其实是：

将 jobinfo 表按照筛选条件 professionRequire='程序员' 执行后作为子查询，再执行 LEFT JOIN ...... ON 。

也就是第一步变成了执行 SELECT * FROM jobinfo AS j WHERE j.professionRequire='程序员'

然后再进行连接查询，也就是

整个sql语句其实变成了：

SELECT * 
FROM userinfo AS u 
LEFT JOIN   
(SELECT *  FROM jobinfo  WHERE jobinfo.professionRequire='程序员') AS j 
ON 
u.userProfession=j.professionRequire

这样查询出来，显然不是我们想要的结果。

那么我们在使用 LEFT JOIN ...... ON ...... 拼接筛选条件时，我们应该怎么做？

配合 WHERE 使用：

SELECT * 
FROM userinfo AS u 
LEFT JOIN   jobinfo AS j 
ON u.userProfession=j.professionRequire
WHERE j.professionRequire='程序员'

结果：

我们把筛选条件配合where去使用，执行的逻辑就是：

先执行LEFT JOIN ...... ON ...... 先将关联两个表之后的数据查询出来；

再按照 professionRequire='程序员' 条件，进行数据筛选。

所以这是我们想要得到的结果。

这是一个使用 LEFT JOIN 的 ON 初学者很容易犯的错误，大家稍微注意点。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。

加载全部内容