隐式外连接转ANSI连接：为何staff_id需从WHERE移至ON子句？

为什么sr.staff_id = :staff_id必须移到ON子句？ #

原隐式连接用逗号分隔表+WHERE条件的写法，本质是内连接逻辑——WHERE里的sr.staff_id = :staff_id会直接过滤掉所有staff表中没有对应staff_role记录的行。但改成ANSI左外连接后，你的业务需求是保留staff表中符合条件的所有行，哪怕没有匹配的staff_role记录：

• 如果把sr.staff_id = :staff_id留在WHERE子句，左外连接返回的staff表无匹配行（此时sr.staff_id为NULL）会因为不满足该条件被过滤，相当于又变回内连接，导致返回行数变少。
• 把这个条件移到ON子句，是让左外连接在关联匹配时就只找staff_role中符合staff_id条件的记录，staff表中没有对应匹配的行依然会被保留（staff_role相关字段为NULL），这才符合你要的业务逻辑。

条件放ON和WHERE的本质区别 #

• ON子句：作用于表的关联匹配阶段，只控制两个表怎么关联，不会过滤左外连接的主表（staff表）的行。哪怕关联条件不满足，主表的行依旧会留在结果集里，关联表的字段填NULL。
• WHERE子句：作用于关联后的结果集过滤阶段，会把所有不满足条件的行（包括主表无匹配的行）全部过滤掉——因为此时关联表字段是NULL，NULL和任何值比较都不成立。

怎么识别这类需要调整的场景？ #

• 当把隐式连接改成左/右外连接时，分清楚两类条件：
  • 属于关联匹配规则的条件：比如关联两表的外键条件、针对关联表的过滤（需要在关联时就筛选，而非关联后过滤），必须放到ON子句。
  • 属于全局结果集过滤的条件：比如针对主表的过滤、不管关联是否成功都要满足的条件，放在WHERE子句。
• 你的场景里，sr.staff_id = :staff_id是针对关联表staff_role的过滤，且要保留staff表无匹配的行，所以放ON；而c.code_type_id = :app_cd、code_active = 'Y'是code表必须满足的全局条件，不管和staff_role是否关联都要生效，所以放WHERE。
• 快速验证：改完后对比新旧查询的返回行数，如果新查询行数更少，大概率是把本该在ON的条件放到了WHERE，把左外连接“降级”成了内连接。

内容的提问来源于stack exchange，提问作者Eric Brown - Cal