简单的一条SQL，不简单的做事思维 NOT IN 、NOT EXISTS、LEFT JOIN用法差别 ...

我司某程序员：一个简单的查询功能对业务系统没有多大影响...
我：......

看破不说破.....
面对这样的SQL 也难起波澜了 转交给DBA

10年经验程序员写的原语句：
SELECT

mobile

FROM

actdb.act_customer_info

WHERE

(
    create_date BETWEEN '2019-02-25 00:00:00'
    AND '2019-02-26 00:00:00'
)

AND (

id NOT IN (
    SELECT
        customer_id
    FROM
        parttime.ls_bill_info
    WHERE
        create_date BETWEEN '2019-02-25 00:00:00'
    AND '2019-02-26 00:00:00'
)

);
原语句执行时间68秒

DBA优化后语句：
SELECT

mobile

FROM

actdb.act_customer_info a

WHERE

create_date BETWEEN '2019-02-25 00:00:00'

AND '2019-02-26 00:00:00'
AND NOT EXISTS (

SELECT
    1
FROM
    parttime.ls_bill_info b
WHERE
    a.id = b.customer_id
AND b.create_date BETWEEN '2019-02-25 00:00:00'
AND '2019-02-26 00:00:00'

);
DBA优化后语句执行时间31秒

最后自己再花1分钟重写一下吧 ，也是满心惆怅啊......
SELECT

a.mobile,
a.id,
c.customer_id

FROM

actdb.act_customer_info a

LEFT JOIN (

SELECT
    customer_id
FROM
    parttime.ls_bill_info b
WHERE
    b.create_date BETWEEN '2019-02-25 00:00:00'
AND '2019-02-26 00:00:00'

) c ON a.id = c.customer_id
WHERE

a.create_date BETWEEN '2019-02-25 00:00:00'

AND '2019-02-26 00:00:00'
AND c.customer_id IS NULL
本质就是not in 和 not exists和left join的区别
这里就不发相关详细执行计划了，简单但又不简单，相信程序员或者DBA都可以写,但偏偏上线的是最差的一条.......

任重道远，磨砺前行吧~