sql 联合查询相关问题

问题的由来

小猿最近做项目，遇上了一个关于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;

查询结果

经过上面的折腾，小猿的问题解决了。