浅析order by中如何处理null以及order by null的作用
一、order by排序中如何处理null的数据
1、Oracle 里:
order by colum asc 时,null默认被放在最后
order by colum desc 时,null默认被放在最前
nulls first 时,强制null放在最前,不为null的按声明顺序[asc|desc]进行排序
nulls last 时,强制null放在最后,不为null的按声明顺序[asc|desc]进行排序
2、MySql 里
order by colum asc 时,null默认被放在最前
order by colum desc 时,null默认被放在最后
ORDER BY IF(ISNULL(update_date),0,1) null被强制放在最前,不为null的按声明顺序[asc|desc]进行排序
ORDER BY IF(ISNULL(update_date),1,0) null被强制放在最后,不为null的按声明顺序[asc|desc]进行排序
简单的有null值排序:如果想要手动指定null的顺序,可以使用:
(1)将null强制放在最前:if(isnull(字段名),0,1) asc //asc可以省略(默认升序)
(2)将null强制放在最后:
if(isnull(字段名),0,1) desc
if(isnull(字段名),1,0) asc //asc可以省略(默认升序)
(3)对于这种使用方式的理解:
以if(isnull(字段名),0,1)为例,它的意思是将该字段根据是否为null值分成两部分,值为null行相当于得到了一个隐含的排序属性0,值为非null的行相当于得到了一个隐含的排序属性1,在排序的时候,先根据这一隐含属性进行升序排列,因为if(isnull(字段名),0,1)相当于if(isnull(字段名),0,1) asc省略了asc而已,所以自然地,排序字段为null的那些行(隐含排序属性)会在最前。
对于将null强制放在最后的语句的理解以此类推。
3、组函数AVG()算NULL值:select AVG(IFNULL(field,0)) from table;
4、一般情况下人们会想到使用非空值代替NULL,这时使用coalesce()函数把控制设置为0
二、order by null的作用
在SQL中order by null有什么用吗?这是我在一次面试时面试官问我的问题,当时我是懵的。他让我猜一下,我说不排序?没想到被我猜对了
不排序你就别用order by啊!为什么要用order by null?
我们看一下下面这条SQL分析:select * from sc group by sc.Sid
在extra中我们看到 using filesort 外部排序,这肯定是影响SQL性能的。尤其是在用到group by时,即使你不需要排序,MySQL会默认根据分组字段order by。
其实,你根本就不需要排序,无辜拉低了你的SQL性能。此时,就可以考虑一下使用 order by null: select * from sc group by sc.Sid order by null
可以看到外部排序没有了。(即使select得到的数据只有一条,MySQL仍然会using filesort)