数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

eg：

创建表语句：

tab1：
insert into tab1 (id, size) values (1, '10');
insert into tab1 (id, size) values (2, '20');
insert into tab1 (id, size) values (3, '30');
insert into tab1 (id, size) values (4, '11');

tab2：
insert into tab2 (id, size, name) values (1, '10', 'aaa');
insert into tab2 (id, size, name) values (2, '20', 'bbb');
insert into tab2 (id, size, name) values (3, '20', 'ccc');

表1

select * from tab1;

表2

select * from tab2;

分析下面两条 sql

select * from tab1 left join tab2 on (tab1.size = tab2.size)where tab2.name='aaa';

select * from tab1 left join tab2 on (tab1.size = tab2.sizeand tab2.name='aaa');

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

总结：

1. 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面

2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。

on后可跟多个条件，语法如下：

SELECT
                    *
                FROM
                    dws.dws_t_result_tmp2 tmp2
                LEFT JOIN
                   ods.ods_t_tmp1 tmp_a
                ON
                    (tmp2.branchno='WE-BEIJING-1') and ( tmp2.branchno='RE-BEIJING-1')

on后边加like：

select position_name,code,senior_name,parent_code,level from position_name_data pnd right join senior_function_data sfd on regexp_replace(sfd.senior_name,'\\\\','/') like  concat('%',regexp_replace(pnd.position_name,'\\\\','/'),'%')  where senior_name is not null group by position_name,code,senior_name,parent_code,level

参考：

https://www.2cto.com/database/201709/683213.html

https://www.2cto.com/database/201805/742735.html

MySQL多个条件关联，join多个条件关联，on后面大于小于><

介绍

使用join关联表时on后面写多个条件

代码

SELECT
	* 
FROM
	lecture l
	INNER JOIN lecture_limit lt ON l.id = lt.lecture_id
	INNER JOIN student_class sc ON sc.CODE = lt.CODE 
	OR floor( sc.CODE / 1000 ) = lt.CODE 
	OR floor( sc.CODE / 1000000 ) = lt.CODE 
	OR floor( sc.CODE / 100000000 ) = lt.CODE 
	OR floor( sc.CODE / 100000000000 ) = lt.CODE 
	OR floor( sc.CODE / 1000000000000 ) = lt.CODE
	where sc.teacher_id =3

thinkphp组装

$res = $lecture
            ->field('*')
            ->alias('l')
            ->join('lecture_limit lt', 'l.id = lt.lecture.id')
            ->join('student_class sc', 'sc.code = lt.code or floor(sc.code / 1000) = lt.code or floor(sc.code / 1000000) = lt.code or floor(sc.code / 100000000) = lt.code or floor(sc.code / 100000000000) = lt.code or floor(sc.code / 1000000000000) = lt.code')
            ->where($where)
            ->order(['l.id' => 'desc'])
            ->paginate([
                'list_rows' => $size,
                'page' => $page,
            ]);

效果

注意：下面的案例特别重要！请重视！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。

错误结果：

错误原因：

1. 查询语文最高成绩时，没有查到2019-12-01的数据
2. 查询数学最低成绩时，使用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);