SQL同一个字段出现null和0值，有何区别，原因是什么？left join导致null值出现，case when导致0值出现...

-- 1.表结构
-- create table visit_hist(
-- customer_id int comment '客户id'
-- ,visit_date int comment '访问日期'
-- )

-- 2.造测试数据
-- insert into visit_hist(customer_id,visit_date) values
-- (11,11),(11,7),(22,5),(66,8),(55,4),(11,35),(22,32),(33,31),(55,39)

-- with t1 as (
-- select customer_id
-- ,visit_date 
-- from visit_hist vh 
-- where visit_date <30
-- )

-- 每个客户观察日(第30日)之前的最大拜访日期
with t2 as (
select customer_id
,max(visit_date) as max_visit_date 
from visit_hist vh 
where visit_date <30
group by customer_id 
)
-- select * from t2

     
     

      
      
     
     

-- 每个客户在观察日之前的最晚一个拜访日期之后30天内的拜访次数
,t3 as (select 
t2.customer_id
,t2.max_visit_date
,sum(case when t_all.visit_date<t2.max_visit_date+30 then 1 else 0 end) as total_cnt_after
from t2 
left join visit_hist t_all on t2.customer_id=t_all.customer_id
where t_all.visit_date >=30 and t_all.visit_date <90
group by t2.customer_id
)
-- 这一句会产生total_cnt_after等于0的行，主要是由case when判断产生
-- select * from t3;

     
     

      
      
     
     

-- 下面的left join由于主表是t2其中的customer_id比t3多，因此会导致t2的某些行total_cnt_after为null值
-- -- 对观察日之前有过拜访记录的客户打上标签
-- select t2.customer_id
-- ,t3.total_cnt_after
-- ,case when t3.total_cnt_after>0 then 1 else 0 end as is_active
-- from t2
-- left join t3 on t2.customer_id=t3.customer_id; 

-- 打印MySQL版本
-- select version();