MySQL：left join后用on与where的区别

一、前言

  前几天项目中，写SQL时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条，奈何发现还是有两条。在此记录一下，on与where的区别。

二、ON

原始数据展示

SELECT t1.*,t2.* FROM  t_test_staff t1 left join t_test_department t2 on t1.departmentId = t2.id and t;

查询结果

ON后面跟and,左表条件

SELECT t1.*,t2.* FROM  t_test_staff t1 left join t_test_department t2 on t1.departmentId = t2.id and t1.name = '员工1';

查询结果

ON后面跟and,右表条件

SELECT t1.*,t2.* FROM  t_test_staff t1 left join t_test_department t2 on t1.departmentId = t2.id and t2.departmentName = '研发部';

查询结果

  ON 条件用于定义两个表之间的连接条件，它决定了哪些行会被连接在一起。在 LEFT JOIN 中，ON 条件决定了右表（被连接的表）中的哪些行会被匹配到左表（主表）的行。

  在这个例子中，Table1 中的每一行都会出现在结果集中，无论 Table2 中是否有匹配的行。如果 Table2 中有匹配的行，则这些行的数据会被包含进来；如果没有匹配的行，则 Table2 的列会显示为 NULL。

三、where

on后面跟where,左表条件

SELECT t1.*,t2.* FROM  t_test_staff t1 left join t_test_department t2 on t1.departmentId = t2.id where t1.name = '员工1';

查询结果

on后面跟where,右表条件

SELECT t1.*,t2.* FROM  t_test_staff t1 left join t_test_department t2 on t1.departmentId = t2.id where t2.departmentName = '研发部';

查询结果

  WHERE 条件用于过滤结果集，它在连接操作之后应用。这意味着 WHERE 条件会进一步筛选已经通过 ON 条件连接好的结果集。

  在这个例子中，LEFT JOIN 依然会先执行，将 Table1 中的每一行与 Table2 中的匹配行连接起来（如果有的话）。然后，WHERE 条件会过滤掉那些 Table2.some_column 为 NULL 的行。

  由于 LEFT JOIN 保证了 Table1 中的每一行都会出现在结果集中，WHERE 条件实际上将结果集转换为了一个类似于 INNER JOIN 的结果集，因为它排除了所有 Table2 中没有匹配行的 Table1 行（即 Table2 列值为 NULL 的行）。

四、总结

  ON 条件：用于定义连接条件，决定如何连接两个表。在 LEFT JOIN 中，它会保留左表中的所有行，并根据条件匹配右表中的行。

  WHERE 条件：用于过滤结果集，它在连接操作之后应用。在 LEFT JOIN 中，使用 WHERE 条件可以进一步筛选结果集，但需要注意它会排除所有不满足条件的行，包括那些由于 LEFT JOIN 而产生的 NULL 值行。