Left Join 之后数据变少

Left Join 之后数据变少

最近在项目上遇到一个查询语句 发现通过left join 查询之后数据变少了

评委表
select pb.biaoduanguid as '项目唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '项目编号',pb.biaoduanguid as '标段唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '标段编号',pb.fuyinum as '复议次数',ppl.pw_guid as '评委唯一识别号',ppl.pw_name as '评委姓名',ppl.idnumber as '评委身份证号码',
pl.mobile as 评委手机号,ppl.company as '评委工作单位',pb.kaibiaodate as '评标时间'

from pingbiao_biaoduan pb ,pingbiao_pw_logininfo ppl 

left join epointbid_bameng.pw_lib pl on pl.shengfenzh =ppl.idnumber

where pb.biaoduanguid=ppl.biaoduanguid

and (pb.biaoduanno like '%TZ%' or pb.biaoduanno like '%NK%' or pb.biaoduanno like '%SL%')

and pb.KAIBIAODATE >'2024-06-20 00:00:00.0' and pl.iszzqpw='1'
union all 
select pb.biaoduanguid as '项目唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '项目编号',pb.biaoduanguid as '标段唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '标段编号',pb.fuyinum as '复议次数',ppl.pw_guid as '评委唯一识别号',ppl.pw_name as '评委姓名',ppl.idnumber as '评委身份证号码',
pl.mobile as 评委手机号,ppl.company as '评委工作单位',pb.kaibiaodate as '评标时间'

from epoint_pb7j_bameng_history.pingbiao_biaoduan pb ,epoint_pb7j_bameng_history.pingbiao_pw_logininfo ppl 

left join epointbid_bameng.pw_lib pl on pl.shengfenzh =ppl.idnumber

where pb.biaoduanguid=ppl.biaoduanguid

and (pb.biaoduanno like '%TZ%' or pb.biaoduanno like '%NK%' or pb.biaoduanno like '%SL%')

and pb.KAIBIAODATE >'2021-01-01 00:00:00.0' and pb.KAIBIAODATE <'2024-06-20 00:00:00.0'
and pl.iszzqpw='1'

原因是因为and pl.iszzqpw=‘1’ 这个条件应该写在 on里面

select pb.biaoduanguid as '项目唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '项目编号',pb.biaoduanguid as '标段唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '标段编号',pb.fuyinum as '复议次数',ppl.pw_guid as '评委唯一识别号',ppl.pw_name as '评委姓名',ppl.idnumber as '评委身份证号码',
pl.mobile as 评委手机号,ppl.company as '评委工作单位',pb.kaibiaodate as '评标时间'

from pingbiao_biaoduan pb ,pingbiao_pw_logininfo ppl 

left join epointbid_bameng.pw_lib pl on pl.shengfenzh =ppl.idnumber and pl.iszzqpw='1'

where pb.biaoduanguid=ppl.biaoduanguid

and (pb.biaoduanno like '%TZ%' or pb.biaoduanno like '%NK%' or pb.biaoduanno like '%SL%')

and pb.KAIBIAODATE >'2024-06-20 00:00:00.0'
union all 
select pb.biaoduanguid as '项目唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '项目编号',pb.biaoduanguid as '标段唯一识别号',pb.biaoduanname as '项目名称',pb.biaoduanno as '标段编号',pb.fuyinum as '复议次数',ppl.pw_guid as '评委唯一识别号',ppl.pw_name as '评委姓名',ppl.idnumber as '评委身份证号码',
pl.mobile as 评委手机号,ppl.company as '评委工作单位',pb.kaibiaodate as '评标时间'

from epoint_pb7j_bameng_history.pingbiao_biaoduan pb ,epoint_pb7j_bameng_history.pingbiao_pw_logininfo ppl 

left join epointbid_bameng.pw_lib pl on pl.shengfenzh =ppl.idnumber and pl.iszzqpw='1'

where pb.biaoduanguid=ppl.biaoduanguid

and (pb.biaoduanno like '%TZ%' or pb.biaoduanno like '%NK%' or pb.biaoduanno like '%SL%')

and pb.KAIBIAODATE >'2021-01-01 00:00:00.0' and pb.KAIBIAODATE <'2024-06-20 00:00:00.0'

pl.iszzqpw='1’这个条件把left join后pl.iszzqpw=null的记录全部筛选掉了，所以最后结果变少了，实际效果就是和inner join一样，如果你不想结果变少，这个条件就需要写到on里面。这个例子就说明了where里面的条件和on里面的条件的区别。