MySQl 联合查询时过滤条件放在ON之后和放在WHERE之后的区别

一、前提

       有两个表‘invite_interview’和‘report_interview’，‘invite_interview’为面试邀请表，‘report_interview’为针对面试邀请填写的面试评价表，两个表通过invite_interview_id关联;

       一个面试邀请记录会对应多个面试评价表记录，面试邀请有对应的面试评价表记录且面试评级表中的project_id=1时，状态为面试报告已填写， 面试邀请无对应的面试评价表记录时或者面试邀请有对应的面试评价表记录且面试评级表中的project_id!=1时，状态为面试报告未填写，具体结构和数据如下：

二、需求

  查询面试邀请记录，并返回面试邀请是否填写面试评价表的填写状态;

  即查询invite_interview表和report_interview表的关联数据，report_interview表中的project_id=1时，状态为已填写；report_interview表中无对应数据或者report_interview表中的project_id!=1时，状态为未填写；

 即利用左表联合查询，左表的记录在右表中无对应记录时，右表中返回的记录为null;这样联合查询的结果中右表中有数据时为面试报告已填写状态，数据为null时，为面试报告未填写状态；

 1、 方法一：过滤条件 r.`project_id` = 1 放在on后面

SELECT 
  * 
FROM
  `invite_interview` AS i 
  LEFT JOIN `report_interview` AS r 
    ON i.`invite_interview_id` = r.`invite_interview_id` 
    AND r.`project_id` = 1 
GROUP BY i.`invite_interview_id` ;

2、方法二：过滤条件 r.`project_id` = 1 放在where后面

SELECT 
  * 
FROM
  `invite_interview` AS i 
  LEFT JOIN `report_interview` AS r 
    ON i.`invite_interview_id` = r.`invite_interview_id` 
WHERE r.`project_id` = 1 
GROUP BY i.`invite_interview_id` ;

3、方法一能达到需求，方法二返回的数据会丢失记录；

方法一 将过滤条件放在on后面，是在联合查询之前，先对右表进行查询过滤，将查询过滤的结果作为临时表，再和左表‘invite_interview’进行联合查询；

在on后面添加过滤条件 r.`project_id` = 1 ，在联合查询之前，先查询右表`report_interview`中r.`project_id` = 1的数据返回为临时表，这样过滤了右表中project_id=2等的数据，右表临时表中的数据都为填写了面试评价表的记录，右表临时表和左表主表联合查询，右表中无对应左表的记录便会以null填充，联合查询返回结果集中project_id=1则为已填写状态，project_id is null则为未填写状态；

方法二 将过滤条件放在where后面，是对联合查询后的数据进行过滤，相比过滤条件放在on后面，可能会丢失部分记录；

左表invite_interview和右表report_interview联合查询返回的数据，在where条件中通过过滤条件r.`project_id` = 1,会排除r.`project_id` != 1和r.`project_id` is null的数据,这样返回的数据只是面试邀请已填写的记录，丢失了面试邀请未填写的记录；

三、常见相关误区

1、误区一、左连接查询的返回数据记录条数并非是左表的数据总条数

     左连接查询，以左表为主，右表中无对应左表的记录时，右表以null填充；

     当右表中有多条数据对应左表时，左表的记录和对应右表的多条记录便会联合出现多条记录，这种情况下联合查询的总条数便会多于左表的数据记录；

     当只想返回左表的数据条数时，可通过group by 左表主键即可。

误区二、where 后面添加过滤条件 r.`project_id` != 1,返回的结果集中，除了会过滤联合查询结果中 r.`project_id` = 1的数据，同样会将 r.`project_id` is null的数据过滤掉哦

误区四、on 可以添加过滤条件

    联合查询时，on 后面可以设置两个表的关联字段，还可以添加过滤条件，右表先通过on后面的过滤条件返回结果集为临时表，再将临时表和左表联合查询返回结果集。