sql 联合查询相关问题

￡神魔★判官ぃ 2022-09-05 15:58 181阅读 0赞

# 问题的由来 #

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

查询结果：  
![在这里插入图片描述][7c4a4694e81f4a098858f087e653249e.png]  
说明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;

查询结果  
![在这里插入图片描述][9c84a2d4bfef430489fe7c9122f73a41.png]  
经过上面的折腾，小猿的问题解决了。

[7c4a4694e81f4a098858f087e653249e.png]: /images/20220829/f41a2f34d0aa485a813cf46ac6d528a7.png
[9c84a2d4bfef430489fe7c9122f73a41.png]: /images/20220829/c1e2e4706744475a8a0008e195ced4eb.png