Mysql 中Left/Right join on后面and和where条件查询的差异-Mysql SQL运算符是有优先级

一、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;