一、Mysql中Left/Right join on后面and和where条件查询的差异
1、建两张测试表，一张商户定义表、一张商户操作状态明细表
1）商户定义表
CREATE TABLE hope.merchant (
MERCHANT_id varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2）商户操作明细表
CREATE TABLE trade_info (
MERCHANT_NO varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
TRADE_STATUS varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
CREATE_TIME datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2.测试Left/Right join on后面and和where条件查询的差异
普通left join演示
select a.MERCHANT_id,b.MERCHANT_NO,b.TRADE_STATUS from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO;
select count(1) from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO;--》82行数据
结果：可以看到，部分商户在某段时间内事没有任何操作的，所以，左表hope.merchant的商户，没有对应的操作，会用Null补足。
1）Left/Right join on后面带and
select a.MERCHANT_id,b.MERCHANT_NO,b.TRADE_STATUS,b.CREATE_TIME from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO and b.CREATE_TIME>'2018-12-19 19:00:00'；
select count(1) from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO and b.CREATE_TIME>'2018-12-19 19:00:00'; ----》69行数据
2）Left/Right join on后面带where
select a.MERCHANT_id,b.MERCHANT_NO,b.TRADE_STATUS,b.CREATE_TIME from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO where b.CREATE_TIME>'2018-12-19 19:00:00';
select count(1) from hope.merchant a left join hope.trade_info b
on a.MERCHANT_id=b.MERCHANT_NO where b.CREATE_TIME>'2018-12-19 19:00:00'; --》43行数据
3）对比结果
语句一是on and ，一句是 on where，下的条件类似，但是结果却是完全不同。带where的过滤掉了左连接中左表没有任何操作记录的商户号，
但是使用and的却没有，并得到了我想要的结果。Why？？？
二、Mysql 运算符是有优先级
1.总结SQL优先级
where是对已经搜索出的结果的过滤条件
on是两者join形成新表时用到的匹配条件
大家可能看出一些规律,在on的情况下and（或者or）与on同时对前面的集合起作用,而存在where的时候,先对集合进行on条件的抽取,在根据where的条件进行抽取.这是个值得注意的地方.
说下原因:
(1)ON后面的筛选条件主要是针对的是关联表【而对于主表筛选条件不适用】；
(2)对于主表的筛选条件应放在where后面，不应该放在ON后面；
(3)对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件放置于ON后；如果是想再连接完毕后才筛选就应把条件放置于where后面；
(4)对于关联表我们其实可以先做子查询再做join。
2.总结sql类型,尽量不写子查询
1）对于关联表我们其实可以先做子查询再做join，此SQL运行3.5秒
select MERCHANT_ID,case when rait is null then 0 else rait end
from (
select MERCHANT_ID,
TRUNCATE(count(case when c.trade_status ='SUCCESS' then 1 else null end)*100/count(case when c.trade_status !='FAILED' then 1 else null end),2) rait
from ifpay_ccpay.merchant a left join (
select * from ifpay_ccpay.trade_info b where b.CREATE_TIME > date_add(now(), interval - 30 minute)
and b.method='H5') c on a.MERCHANT_ID=c.MERCHANT_NO group by a.MERCHANT_ID) d;
2）不做子查询运行14ms
select MERCHANT_ID,case when rait is null then 0 else rait end
from (
select MERCHANT_ID,
TRUNCATE(count(case when b.trade_status ='SUCCESS' then 1 else null end)*100/count(case when b.trade_status !='FAILED' then 1 else null end),2) rait
from ifpay_ccpay.merchant a left join ifpay_ccpay.trade_info b on a.MERCHANT_ID=b.MERCHANT_NO
and b.CREATE_TIME > date_add(now(), interval - 30 minute)
and b.method='H5' group by a.MERCHANT_ID) D;
