SQL语句主查询和子查询结果不一致

Question

select count(*) from basic_jyj_xsjbxxb where sfzh in ( ----56371
select sfzh from ( --56777
select stu.sfzh
from basic_jyj_xsjbxxb stu inner join BASIC_GA_LDRKDJXX_WZ ld on ld.sfzhm=stu.sfzh where stu.xd='小学' and SSJD is not null
--查询流动人口中各街道人口结构
union all
select stu.sfzh
from basic_jyj_xsjbxxb stu inner join BASIC_GA_CZRKDJXX_WZ cz on cz.sfzhm=stu.sfzh where stu.xd='小学' and xzjd is not null
--查询常住人口中在各街道人口结构
)
)
这是主查询，查询结果是 56371

单独查询里面的子查询,查询结果是 56777
select count(sfzh) from ( --56777
select stu.sfzh
from basic_jyj_xsjbxxb stu inner join BASIC_GA_LDRKDJXX_WZ ld on ld.sfzhm=stu.sfzh where stu.xd='小学' and SSJD is not null
--查询流动人口中各街道人口结构
union all
select stu.sfzh
from basic_jyj_xsjbxxb stu inner join BASIC_GA_CZRKDJXX_WZ cz on cz.sfzhm=stu.sfzh where stu.xd='小学' and xzjd is not null
--查询常住人口中在各街道人口结构
)
注：sfzh没有重复数据，没有空数据

需求：为什么两次的结果会不一致？

Answer 1

你把子查询的union all 改成union 试试 ，可能是两个union all 的结果之间有重复