MySQL数据库查询（left join 或right join 加上where条件后连接失效，等同于inner join） MySQL

有一个年都月份临时表，一个每月金额结果表，通过left join所希望的最终结果为：每个月金额多少，如果这个没有金额，则为0。
月份临时表：
SELECT DISTINCT bgbh,nd,yf FROMSJQX.t_bg_yflsb WHERE BGBH = 'BG202004260001'

文章图片

金额结果表：
SELECT * FROM SJQX.T_BG_00003 T WHERE T.BGBH = 'BG202004260004';

文章图片

通过LEFT JOIN 语句得到的结果为：

SELECT CASE WHEN T.ynse IS NULL THEN 0 ELSE T.ynse END AS ynse, CASE WHEN T.snse IS NULL THEN 0 ELSE T.snse END AS snse, T.yf as sj,T.nd, CASE WHEN T.sbfs IS NULL THEN '2' ELSE T.sbfs END AS sbfsFROM ( SELECTT1.ynse,T1.snse,T2.yf,T2.nd,T1.sbfs FROM (SELECT DISTINCT bgbh,nd,yf FROMSJQX.t_bg_yflsb WHERE BGBH = 'BG202004260001') t2 LEFTJOIN SJQX.T_BG_00003T1 ONT1.sm = T2.yf and T1.nd =t2.nd WHERET1.BGBH = 'BG202004260004') T ORDER BY nd,yf;

MySQL数据库查询（left join 或right join 加上where条件后连接失效，等同于inner join）

文章图片

没有得到如果在金额表里匹配到得到金额数值，如果没有匹配到则默认为空展示0的效果。
原因：where是先连接然后再筛选。
解决方法：将where 换成and 即：

SELECT CASE WHEN T.ynse IS NULL THEN 0 ELSE T.ynse END AS ynse, CASE WHEN T.snse IS NULL THEN 0 ELSE T.snse END AS snse, T.yf as sj,T.nd, CASE WHEN T.sbfs IS NULL THEN '2' ELSE T.sbfs END AS sbfsFROM ( SELECTT1.ynse,T1.snse,T2.yf,T2.nd,T1.sbfs FROM (SELECT DISTINCT bgbh,nd,yf FROMSJQX.t_bg_yflsb WHERE BGBH = 'BG202004260004') t2 LEFTJOIN SJQX.T_BG_00003T1 ONT1.sm = T2.yf and T1.nd =t2.nd and (T1.BGBH = 'BG202004260004' ) ) T ORDER BY nd,yf;