计算优先级:() > and > or
如果存在小括号,先计算括号里边的
select 1 > 2 and 4 > 3 or 4 < 5
等价于select (1 > 2 and 4 > 3) or 4 < 5
;返回的是true
;select 1 > 2 and 4 > 3 or 4 > 5
返回的是false
,or
的两端都是false
返回false
select 1 > 2 and (4 > 3 or 4 > 5)
返回的是false
,有括号()
,先计算括号里的
给个join关联案例:on a.id=b.id and b.dt='20230600' or b.id is not null where a.id='20230600'
,假设a表和b表都是分区表,单分区数据量很大,写下这条sql,基本可以认为集群要挂掉了; or b.id is not null
只要b表的id非null就符合条件,基本等同于拉取a表分区20230600和b表所有分区id字段做了笛卡尔;
空的几种解释
- 空字符串
''
; - 缺失
- 不合理,比如不同动物信息,尾巴长度字段对某些动物来说就不合理,因为有些动物没有尾巴;
- 不确定
在SQL中,null
通常用不确定来形容比较确切,这意味着null
在逻辑判断中,返回的都是null
select null > 1
返回的是null
。
select 1=null or 1<>null
返回null
。
比如取非空字符串,剔除掉空字符串和null
,我们可以直接:where field_name <> ''
,但在实际编写中最好还是都加上:where field_name <> and field_name is not null
,易读,避免触发意想不到的意外;
有这么一张表tb
id |
1 |
6 |
null |
2 |
select id from tb where id < 6 order by id desc
语句会把null也过滤掉,当id是null时,null < 6
返回的是null
而不是true
所以这条记录不会返回。
返回两条记录:
id |
2 |
1 |
在字段内关联join时,on的任意一边有一个null,这条记录都不会取到;这里要小心一个陷阱,如果是单字段关联,在sql执行器优化中,在执行计划中,比如hive,通常我们可以看到在map端提前添加了field_name is not null
筛选条件;
但如果是多字段关联,其中某字段对等,其他字段是null我们认为也是对等时,这时候我们要考虑把null
统一转化为一个指定值;on a.id=b.id and a.name1=b.name1
,如果id
字段能关联上,但是name1
字段是null,我们认为是对等的,这里要改下下:on a.id=b.id and nvl(a.name1,'')=nvl(b.name1,'')
在做数据聚合时,一般sum
求和,count
计数不会有问题,我们普遍理解,如果遇到null
,把这行记录忽略掉;还是用这个例子:
id |
1 |
6 |
null |
2 |
sum求和是9,计数是3;
但求avg
计算平均数时,有时候可能要注意,即null
,可能也是我们统计范围内,但是函数会将该记录行剔除掉;给个场景:
现在有一张活跃表,active_user,一张付费表pay_user,我们用活跃表id关联付费表id计算活跃用户所在月份的平均付费(活跃arpu),活跃uv;
select count(t1.id) as active_uv -- 活跃uv,假设两张子表id没有重复
,avg(t2.pay) as avg_pay -- 拉取活跃用户平均付费,而实际拉取的是付费arpu
from active_user t1
left join pay_user t2
on t1.id=t2.id annd t2.dt='20230600'
where t1.dt='20230600'
group by id
这时候就会出现问题,平均 = 求和 / 计数,这里求和总量是一样的,有无null都是一样;但是计数的基数是活跃用户,而不是付费用户平均付费,因为pay
字段缺失,实际求的是付费arpu;
and和or语句存在null的优先级
在and中:false > null > true,这意味着在and两侧,只要有一个false
则返回false
,没有false
存在null
则返回null
,只有左右都是true
时才返回true
。
select 2 > 1 and null
返回null
。select 2 > 3 and null
返回false
。
在or关系中,true > null > false,这意味着在or
两侧,只要存在一个为true
则返回true
,没有true
,存在null
则返回null
。select 1 < 2 or null
返回true
。select 1 > 2 or null
返回null
。select 1 in (2,3,null)
等同于select 1=2 or 1=3 or 1=null
返回null
。