MySQL的JOIN语句查询条件在WHERE子句和ON子句中的区别

一切的一切都是源于给表加了个字段
maxresdefault.jpg

References:

背景:
之前一直好好的一段代码,今天忽然报错了。于是检查修改记录,发现是因为一段SQL被修改了,而修改的原因是为了以后扩展。为了以后扩展更容易,于是给一个账户表添加了状态(status)字段,方便控制账户的状态。由于目前所有的账号都是ACTIVE的状态,于是在文件中直接将相关SQL添加了一个查询条件:AND status = "ACTIVE",根据标题,当然也包括了JOIN的查询,于是就出错了。检查后发现是因为我把状态的条件放在WHERE子句后面,老大说这里放到ON子句后面就行了。经过我的一番查询和模拟测试,发现放在WHERE和ON后面的区别,所以记录一下这个坑。

先说说业务流程吧,用户填写一些信息并提交一个开账号的申请,然后后台人工审核,如果拒绝则流程结束,如果审核通过则会给一个预分配帐号(未激活不可用),用户拿到预分配账号后再进行一些操作即可激活账号正常使用,申请流程完成。为了方便重现错误以及方便理解,建了几个模拟表,并放了点测试数据。

  • 开户申请表(test_opening_status)▼,记录用户申请账号的状态,这里从下向上看,一个刚提交的申请(SUBIT),一个审核通过(APPROVE),一个审核拒绝(REJECT),两个完成(COMPLETE)。

    Snipaste_2019-12-16_18-10-30.png

  • 申请信息表(test_apply_info)▼,记录用户的申请信息,open_status_id对应开户申请表的id,prealloc_account_no是预分配的账号,所以只有申请状态为"APPROVE"或"COMPLETE"的才会有预分配账号,store_link是用户填写的信息,忽略。
    Snipaste_2019-12-16_18-12-56.png
  • 账号明细表(test_account_detail)▼,存放账号具体信息,open_status_id对应开户申请表的id,account_number是已激活的账号,这个表中没有预分配账号,全部都是真实可用的账号,最后的状态就是为了方便将来扩展新加的字段。
    Snipaste_2019-12-16_18-12-32.png

总结下这些数据,一个用户(user_id = 1)提交了5个开户申请,对应5条开户申请记录和5条申请信息记录,其中2个申请已经完成,所以账号明细表中存放了两个账号,一个审核通过,所以只给了预分配账号,一个被拒绝,忽略,一个刚提交申请,忽略。

现在需求如下:
查询一个用户(比如user_id = 1)所有账号的信息(包括预分配账号的信息),并展示出账号对应的store_link。
我一开始写的SQL和查询结果如下:
Snipaste_2019-12-16_18-49-11.png

这是账号明细表没有status的情况,现在加了status,我将条件放在了WHERE后面,如下:
Snipaste_2019-12-16_18-51-55.png

问题出现了,用户看不到申请的预分配账号了!
将条件放在ON子句后面是这样的:
Snipaste_2019-12-16_20-11-13.png

那为什么会这样呢,将WHERE后的查询先去除看看生成的中间表
Snipaste_2019-12-16_20-14-53.png

这样就能看出来了,以开户申请表为主表,账户明细表是关联表,不管ON后面条件是true还是false,主表中的记录都会展示。所以当status="ACTIVE"在ON后面时,关联查询会查不出账户明细表的账号,但是可以查出来申请表的预分配账号信息。当status="ACTIVE"在WHERE后面时,会对生成的临时表再做一次过滤,只找出status="ACTIVE"的账号,也就造成了查询结果的不同,这些都是源于JOIN的特殊性。

标签: none

添加新评论

ali-01.gifali-58.gifali-09.gifali-23.gifali-04.gifali-46.gifali-57.gifali-22.gifali-38.gifali-13.gifali-10.gifali-34.gifali-06.gifali-37.gifali-42.gifali-35.gifali-12.gifali-30.gifali-16.gifali-54.gifali-55.gifali-59.gif

加载中……