问题的由来
小猿最近做项目,遇上了一个关于sql联合查询的问题,sql联合查询往往会遇到比较糟糕,
以下时小猿遇到的问题:
SELECT
whcbin.whcbin_id as whcbin_id,
whcbin.bin_code as bin_code,
whcbin.whc_id as whc_id,
whcbin.stop_flag as stop_flag,
whcbin.del_flag as del_flag,
whcbin.created_by as created_by,
whcbin.created_time as created_time,
whcbin.updated_by as updated_by,
whcbin.updated_time as updated_time,
whc.wh_position as wh_position
FROM
lg_config_whbin whcbin
left join
lg_config_wh whc
on
whcbin.whc_id = whc.whc_id
and
whcbin.stop_flag =1
and
(
whcbin.del_flag = '0' or
whcbin.del_flag is null )
ORDER BY whcbin.updated_time desc;
查询结果:
说明and后面的whcbin.stop_flag =1根本就没有其作用,说明在使用left join on来联合查询时,on后面加上一切条件都失效了, 这是为啥呢?
因果
其本质原因是LEFT JOIN ON生成临时表时所用的条件,不管on中的条件是否为真,都会返回左边表中的记录,故在ON后面再次加入条件就不起任何作用,而where条件是在临时表生成好后再对临时表进行过滤的条件,所以where后面的条件就能达到小猿的数据过滤目的。
问题的解决
小猿对上面的sql做了修改,用以下sql:
SELECT
whcbin.whcbin_id as whcbin_id,
whcbin.bin_code as bin_code,
whcbin.whc_id as whc_id,
whcbin.stop_flag as stop_flag,
whcbin.del_flag as del_flag,
whcbin.created_by as created_by,
whcbin.created_time as created_time,
whcbin.updated_by as updated_by,
whcbin.updated_time as updated_time,
whc.wh_position as wh_position
FROM
lg_config_whbin whcbin
left join
lg_config_wh whc
on
whcbin.whc_id = whc.whc_id
where 1=1
and (
whcbin.del_flag = '0' or
whcbin.del_flag is null )
and
whcbin.stop_flag =1
ORDER BY whcbin.updated_time desc;
或者
select
whcbin.whcbin_id as whcbin_id,
whcbin.bin_code as bin_code,
whcbin.whc_id as whc_id,
whcbin.stop_flag as stop_flag,
whcbin.del_flag as del_flag,
whcbin.created_by as created_by,
whcbin.created_time as created_time,
whcbin.updated_by as updated_by,
whcbin.updated_time as updated_time,
whc.wh_position as wh_position
from
lg_config_wh whc
right join
(SELECT * from
lg_config_whbin
where
stop_flag ='0'
and
(del_flag = '0'
or
del_flag
is null
)
) whcbin
on
whcbin.whc_id = whc.whc_id
ORDER BY whcbin.updated_time desc;
查询结果
经过上面的折腾,小猿的问题解决了。