Mysql左连接查询问题

先说总结

总结：在left join语句中，左表过滤必须放在where条件中，右表过滤必须放在on条件中

执行left join sql

-- 其中P1是on过滤条件，缺失则认为是TRUE，P2是where过滤条件，缺失也认为是TRUE
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT) WHERE P2(LT,RT);

执行left join时的伪代码

FOR each row lt in LT {// 遍历左表的每一行
  BOOL b = FALSE;
  FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行，找到满足join条件的行
    IF P2(lt, rt) {//满足 where 过滤条件
      t:=lt||rt;//合并行，输出该行
    }
    b=TRUE;// lt在RT中有对应的行
  }
  IF (!b) { // 遍历完RT，发现lt在RT中没有有对应的行，则尝试用null补一行
    IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
      t:=lt||NULL; // 输出lt和null补上的行
    }         
  }
}

释义

• 1.右表限制用ON如果想对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失，导致左表在右表中无匹配行的行在最终结果中不出现，违背了我们对left join的理解。因为对左表无右表匹配行的行而言，遍历右表后b=FALSE,所以会尝试用NULL补齐右表，但是此时我们的P2对右表行进行了限制，NULL若不满足P2(NULL一般都不会满足限制条件，除非IS NULL这种)，则不会加入最终的结果中，导致结果缺失。
• 2.左表限制用WHERE如果没有where条件，无论on条件对左表进行怎样的限制，左表的每一行都至少会有一行的合成结果，对左表行而言，若右表若没有对应的行，则右表遍历结束后b=FALSE，会用一行NULL来生成数据，而这个数据是多余的。所以对左表进行过滤必须用where。

示例

错误写法

右表过滤放在WHERE条件中 P2含有右表过滤 右表含有NULL数据导致整体数据减少

SELECT t2.c_fullname, t2.c_company_no, t2.c_id, t1.c_company_id, count(t1.c_id) FROM `ehr-ucenter`.t_company t2
LEFT JOIN `ehr-employee`.view_t_employee_info t1
ON t1.c_company_id = t2.c_id 
WHERE t2.c_id in (SELECT c_company_id FROM `ehr-ucenter`.t_group_company WHERE c_group_id = 'd668de3c45784438928bd4ef710ae346')
AND t1.c_is_delete = 0 AND t1.c_work_status in (1,2,3)
GROUP BY t2.c_id
ORDER BY t2.c_id;

正确写法

右表过滤必须放在on条件中

SELECT t2.c_fullname, t2.c_company_no, t2.c_id, t1.c_company_id, count(t1.c_id) FROM `ehr-ucenter`.t_company t2
LEFT JOIN `ehr-employee`.view_t_employee_info t1
ON t1.c_company_id = t2.c_id 
AND t1.c_is_delete = 0 AND t1.c_work_status in (1,2,3)
WHERE t2.c_id in (SELECT c_company_id FROM `ehr-ucenter`.t_group_company WHERE c_group_id = 'd668de3c45784438928bd4ef710ae346')
GROUP BY t2.c_id
ORDER BY t2.c_id;

参考文档

阿里云-开发者社区