这破站 MySQL的JOIN语句查询条件在WHERE子句和ON子句中的区别
一切的一切都是源于给表加了个字段
References:
背景：
之前一直好好的一段代码，今天忽然报错了。于是检查修改记录，发现是因为一段SQL被修改了，而修改的原因是为了以后扩展。为了以后扩展更容易，于是给一个账户表添加了状态(status)字段，方便控制账户的状态。由于目前所有的账号都是ACTIVE的状态，于是在文件中直接将相关SQL添加了一个查询条件：AND status = "ACTIVE"，根据标题，当然也包括了JOIN的查询，于是就出错了。检查后发现是因为我把状态的条件放在WHERE子句后面，老大说这里放到ON子句后面就行了。经过我的一番查询和模拟测试，发现放在WHERE和ON后面的区别，所以记录一下这个坑。
先说说业务流程吧，用户填写一些信息并提交一个开账号的申请，然后后台人工审核，如果拒绝则流程结束，如果审核通过则会给一个预分配帐号(未激活不可用)，用户拿到预分配账号后再进行一些操作即可激活账号正常使用，申请流程完成。为了方便重现错误以及方便理解，建了几个模拟表，并放了点测试数据。
- 开户申请表(test_opening_status)▼，记录用户申请账号的状态，这里从下向上看，一个刚提交的申请(SUBIT)，一个审核通过(APPROVE)，一个审核拒绝(REJECT)，两个完成(COMPLETE)。
- 申请信息表(test_apply_info)▼，记录用户的申请信息，open_status_id对应开户申请表的id，prealloc_account_no是预分配的账号，所以只有申请状态为"APPROVE"或"COMPLETE"的才会有预分配账号，store_link是用户填写的信息，忽略。
- 账号明细表(test_account_detail)▼，存放账号具体信息，open_status_id对应开户申请表的id，account_number是已激活的账号，这个表中没有预分配账号，全部都是真实可用的账号，最后的状态就是为了方便将来扩展新加的字段。
总结下这些数据，一个用户(user_id = 1)提交了5个开户申请，对应5条开户申请记录和5条申请信息记录，其中2个申请已经完成，所以账号明细表中存放了两个账号，一个审核通过，所以只给了预分配账号，一个被拒绝，忽略，一个刚提交申请，忽略。
现在需求如下：
查询一个用户(比如user_id = 1)所有账号的信息(包括预分配账号的信息)，并展示出账号对应的store_link。
我一开始写的SQL和查询结果如下：
这是账号明细表没有status的情况，现在加了status，我将条件放在了WHERE后面，如下：
问题出现了，用户看不到申请的预分配账号了！
将条件放在ON子句后面是这样的：
那为什么会这样呢，将WHERE后的查询先去除看看生成的中间表
这样就能看出来了，以开户申请表为主表，账户明细表是关联表，不管ON后面条件是true还是false，主表中的记录都会展示。所以当status="ACTIVE"在ON后面时，关联查询会查不出账户明细表的账号，但是可以查出来申请表的预分配账号信息。当status="ACTIVE"在WHERE后面时，会对生成的临时表再做一次过滤，只找出status="ACTIVE"的账号，也就造成了查询结果的不同，这些都是源于JOIN的特殊性。