SQL：两表关联取舍时， is null 判断只能单独存在

最新推荐文章于 2023-03-02 10:33:34 发布

原创最新推荐文章于 2023-03-02 10:33:34 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

SQL 专栏收录该内容

11 篇文章

订阅专栏

本文探讨了SQL中量表关联的正确实践，强调了isnull判断的独立使用原则，对比了错误与正确的SQL语句写法，指导如何高效且准确地进行数据筛选。

SQL：量表关联取舍时， is null 判断只能单独存在，不能与其他判断同时进行。

错误的写法：


select 
a.user_id
,a.uid
,a.page_type
,a.d

from
(
    select 
    o.user_id
    ,f.uid
    ,f.page_type
    ,f.d
    from dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f
    right join dw_busdb.tbl_order o
    on o.d = f.d  and  o.user_id = f.uid 
    where f.d = '2019-08-05'    ---这边的写法也不好，最好写嵌套20190807
    and f.page_type = 'list'   
    and to_date(o.create_dttm) = '2019-08-05'
    and f.uid is not  null  
  	and f.uid <> '' --''不考虑20190806
    and (o.user_id is not null and o.user_id <> '')  -- 订单表本身不为null
)a
left join dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f_pkg
on  a.user_id = f_pkg.uid and a.d = f_pkg.d
where f_pkg.page_type = 'servicePkg'  -- 这样的写法是错误的
	and f_pkg.uid is null

正确的写法：

select 
distinct a.user_id

from
(
    select 
    o.user_id

    from dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f
    join dw_busdb.tbl_order o  -- join会默认跳过null数据
    on o.d = f.d  and  o.user_id = f.uid 
    where f.d = '2019-08-06'   -- 这样写效率不高，最好先写子查询，也能有效避免出错
    and f.page_type = 'list'
    and to_date(o.create_dttm) = '2019-08-06'

  	and f.uid <> '' --''不考虑20190806
   	and order_business_type = 'bus'

)a


left join 
(select  f_pkg.uid
from 
dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f_pkg
 where  f_pkg.d = '2019-08-06'
and  f_pkg.page_type = 'servicePkg'
 )b
on a.user_id = b.uid
where b.uid is null  -- 此处判断没问题，null判断不能与其他判断同时存在。