社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL留疑问:left join时选on还是where?

鸭哥聊Java • 4 月前 • 201 次点击  

订单列表少了一半,接口没报错,分页也正常。 最后翻 SQL,问题就藏在一行:

left join pay_record p on p.order_id = o.id
where p.status = 'SUCCESS'

这 SQL 第一眼看着没毛病,左连接嘛,订单表在左边,支付记录在右边。结果一跑,没支付记录的订单全没了。

这地方我一般不会先怀疑 Java 分页,也不会先怀疑 MyBatis。先把 SQL 拿出来单独跑,再把 where 里的右表条件挪一挪,结果通常就出来了。

比如订单表:

select
    o.id,
    o.order_no,
    o.user_id,
    p.pay_no,
    p.status
from t_order o
left join t_pay_record p
    on p.order_id = o.id
where o.user_id = 1001;

这个没什么争议。

o.user_id = 1001 是左表条件,放在 where 里很自然。意思是:我只查这个用户的订单,不管有没有支付记录,都要把订单查出来。

但如果改成这样:

select
    o.id,
    o.order_no,
    p.pay_no,
    p.status
from t_order o
left  join t_pay_record p
    on p.order_id = o.id
where o.user_id = 1001
  and p.status = 'SUCCESS';

这就变味了。

left join 本来会把左表数据保留下来,右表匹配不上时,右表字段补 null。可你后面又写了:

p.status = 'SUCCESS'

右表都补成 null 了,当然不可能等于 SUCCESS。于是这些没支付记录的订单,在 where 阶段被干掉了。

这时候它看起来写的是 left join,跑出来的效果更像  inner join。

如果业务要的是“查用户订单,并且只带成功支付记录;没支付也要显示订单”,我会写成这样:

select
    o.id,
    o.order_no,
    p.pay_no,
    p.status
from t_order o
left join t_pay_record p
    on p.order_id = o.id
   and p.status = 'SUCCESS'
where o.user_id = 1001;

注意,p.status = 'SUCCESS' 挪到了 on 后面。

这个语义就稳了: 先限定右表只拿成功支付记录来匹配;匹配不上也没关系,订单还在。

这就是 on 和 where 最大的区别。

on 管的是“怎么匹配右表”。 where 管的是“最终结果要不要留下”。

这句话别背,背了也容易忘。线上排问题时直接看右表字段在哪里过滤。

我之前见过一个查询售后单的接口,代码里大概是这种写法:

public List queryOrders(Long userId, String payStatus) {
    String sql = """
        select
            o.id,
            o.order_no,
            o.created_at,
            p.pay_no,
            p.status as pay_status
        from t_order o
        left join t_pay_record p on p.order_id = o.id
        where o.user_id = ?
          and p.status = ?
        order by o.created_at desc
        "
"";

    return jdbcTemplate.query(sql, new OrderViewMapper(), userId, payStatus);
}

测试同学说“未支付订单不展示了”。 开发第一反应是前端状态判断错了,我不太信。因为前端再怎么判断,后端返回条数已经少了。

我一般会先把 SQL 改成这样:

public List queryOrders(Long userId, String payStatus) {
    String sql = """
        select
            o.id,
            o.order_no,
            o.created_at,
            p.pay_no,
            p.status as pay_status
        from t_order o
        left join t_pay_record p
               on p.order_id = o.id
              and p.status = ?
        where o.user_id = ?
        order by o.created_at desc
        "
"";

    return jdbcTemplate.query(sql, new OrderViewMapper(), payStatus, userId);
}

这里参数顺序也要跟着改,不然又是另一个坑。

如果用 MyBatis,最容易写坏的是动态条件:

<select id="pageOrder" resultType="OrderView">
    select
        o.id,
        o.order_no,
        p.pay_no,
        p.status as pay_status
    from t_order o
    left join t_pay_record p
           on p.order_id = o.id
    where o.user_id = #{userId}
    <if  test="payStatus != null and payStatus != ''">
        and p.status = #{payStatus}
    if>
select>

这个写法只要 payStatus 有值,左连接就被你自己拆了。

我更愿意这样写:

<select id="pageOrder" resultType="OrderView">
    select
        o.id,
        o.order_no,
        p.pay_no,
        p.status as pay_status
    from t_order o
    left join t_pay_record p
           on p.order_id = o.id
          <if test="payStatus != null and payStatus != ''">
             and p.status = #{payStatus}
          if>
    where o.user_id = #{userId}
select>

这个版本至少语义没乱。

当然,不是所有右表条件都必须放 on。

如果业务就是要“只查已经成功支付的订单”,那你写:

where p.status = 'SUCCESS'

也没问题。甚至直接写 inner join 更干脆:

select
    o.id,
    o.order_no,
    p.pay_no
from t_order o
inner join t_pay_record p
    on p.order_id = o.id
where o.user_id = 1001
  and p.status = 'SUCCESS';

我比较嫌弃那种明明要内连接,非写个 left join,然后在 where 里把右表 null 过滤掉的 SQL。不是不能跑,是后面接手的人会误判业务含义。

还有一种情况也常见:

where p.status <> 'CLOSED'

这个也会过滤掉右表为 null 的记录。因为 null <> 'CLOSED' 不是 true。

如果你确实想保留没匹配到右表的数据,要写清楚:

where p.status <> 'CLOSED'
   or p.id is null

但我一般不优先这么写。能放  on 的右表匹配条件,先放 on,SQL 意图更清楚。

再看执行计划时,也别只看有没有走索引。先看返回行数是不是符合业务。

我排这种问题,经常会先临时加两列:

select
    o.id,
    o.order_no,
    p.id as pay_id,
    casewhen p.id isnullthen1else0endas no_pay
from t_order o
leftjoin t_pay_record p
    on p.order_id = o.id
   and p.status = 'SUCCESS'
where o.user_id = 1001;

no_pay = 1 的数据还在,说明左连接语义没被破坏。 如果一条都没了,就去查 where,十有八九是右表条件放错了。

最后记一个现场规则就够了:

左表过滤,放 where。 右表参与匹配,但不想丢左表数据,放  on。 右表条件放 where,通常意味着你接受丢掉右表匹配不上的左表记录。

这不是语法洁癖,是结果集会不会少数据的问题。线上分页少几条,用户不会告诉你 SQL 写得不优雅,只会说订单丢了。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/198471