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里面的条件的区别。