Oracle与Sqlserver：Order by NULL值介绍

针对页面传参到in的子集中去进行查询操作的话，就会有in(xxx,null)，这样就会导致查询的结果中其实直接过滤掉了null，根本就查不出来null的值。之前对于null的操作都是进行不同数据库的null函数来进行选择nvl、isnull、ifnull等，直接将字段的null进行转换后再操作。

只知道要对数据库中的null进行转换的操作，但是不知所云，所以今天就大致了解下。针对oracle的null的基本操作：

一、null值的介绍

NULL 是数据库中特有的数据类型，当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。

二、oracle中的null值介绍

在不知道具体有什么数据的时候，即未知，可以用NULL， 称它为空，ORACLE中，含有空值的表列长度为零。允许任何一种数据类型的字段为空，除了以下两种情况：

a、主键字段（primary key）；

b、定义时已经加了NOT NULL限制条件的字段

三、Oracle中null值说明：

a、等价于没有任何值、是未知数。

b、NULL与0、空字符串、空格都不同。

c、对空值做加、减、乘、除等运算操作，结果 仍为空。

d、NULL的处理使用NVL函数。

e、比较时使用关键字用“is null”和“is not null”。

f、空值不能被索引，所以查询时有些符合条件的数据可能查不出来， count(expr)中，用nvl(列名,0)处理后再查。

g、排序时比其他数据都大（索引默认是降序排列，小→大）， 所以NULL值总是排在最后。

IS NULL 和IS NOT NULL 是不可分割的整体，改为IS 或IS NOT都是错误的，从上面我们看到了NULL 和空字符串的区别。

任何和NULL 的比较操作，如<>、=、<=等都返回UNKNOWN（这里的unknown就是null，它单独使用和布尔值false类似）.判断和比较规则总结如下：

四、null做一些算术运算

比如+，-，*，/等，结果 还是null，但是对于连接操作符||，null忽略,concat函数也忽略null

五、null相关函数规则

Oracle有nvl、nvl2、nullif、coalesce等函数专门处理null

nvl（expr1,expr2）：如果expr1是null，那么用expr2作为返回值，不是null则返回expr1.expr1与expr2一般是类型相同的，如果类型不同则会采用自动转换，转换失败则报错。

nvl2（expr1,expr2,expr3)：expr1如果是null，则返回expr3,否则返回expr2，expr2和expr3类型不同，expr3类型转换为expr2类型

nullif(expr1,expr2)：判断expr1和expr2是否相等，若相等则返回null，否则返回expr1.要求expr1与expr2类型必须相同

coalesce（expr1,expr2,…,exprn)：从左到右返回第一个为非null的值，若所有的列表元素都为null，则返回null。要求所有都必须为同一类型。

六、null与索引

Oracle中的B*Tree索引，并不存储全为null的列，虽然在表中建立了符合UNIQUE 索引，但是全为null的行还是可以插入的，而不是全为null的重复行则不可以插入。因为在UNIQUE约束中，（null，null）和（null，null）是不同的，当然在其他一些情况，比如说分组、集合操作中都认为全是null是相等的

七、null的排序

order by默认升序（asc）,这时候null是排在最后的，如果指定降序那么null是排在最前面的，认为null最大。

但是可以用nulls first和nulls last进行调整。order by comm asc nulls first/last

八、null与性能的关系

Not null约束，定义约束是要付出消耗性能的代价的，由下面的测试可以看出虽然约束检查的很快，但是有时候还是很消耗资源的，至少在这个例子上是这样的，不需要not null约束，除非必要，不要乱定义约束。

九、动态语句中的绑定变量与null

在PL/SQL中动态SQL和动态PL/SQL经常使用绑定变量，这个绑定变量有个要求，就是不能直接传入字面量null值，因为PL/SQL中动态语句要求传入的绑定变量必须是SQL类型，而字面量null是无类型的，null字面量传入是不可以的。

当然可以采用多种方法，如果一定要传入null，则可以将null改为空字符串、TO_NUMBER,TO_CHAR,TO_DATE等函数进行转换，或定义一个未初始化的变量、直接传入变量等。即不能定义一参数赋值null，必须先给予其他的赋值结果。

针对sqlserver的null的基本操作：

一、使用 =null / <>null 默认情况下的确不能使用 =null / <> null 来判断 null 值如此。实际上 SQL Server 可以 使用 SET ANSI_NULLS { ON | OFF } 设定来控制 =null / <>null 的行为。

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL的 SELECT 语句仍返回零行。

即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行

但是当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。

使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。

使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。

此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL的行。

二、 改变 null 值的连接行为 SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL { ON | OFF } 来控制null 与其它字符串连接的行为。

当 SET CONCAT_NULL_YIELDS_NULL 为 ON 时，串联空值与字符串将产生 NULL 结果。例如， SELECT 'abc' + NULL 将生成 NULL 。

当 SET CONCAT_NULL_YIELDS_NULL 为 OFF 时，串联空值与字符串将产生字符串本身（空值作为空字符串处理）。例如， SELECT 'abc' + NULL 将生成 abc 。

如果未指定 SET CONCAT_NULL_YIELDS ，则应用 CONCAT_NULL_YIELDS_NULL 数据库选项的设置。

注：在 SQL Server 的未来版本中， CONCAT_NULL_YIELDS_NULL 将始终为 ON ，而且将该选项显式设置为 OFF 的任何应用程序都将产生一个错误。

三、变量的默认值与 null 值

命名一个变量后，如果没有给它赋初始值，它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ‘null’ 非彼 ‘null’ 。

1. 子查询中的 null

子查询中出现的 null 值经常会被我们忽视。

2. Case 语句中的 null

Case 中的 when 语句注意不要写成 when null, 否则得不到想要的结果。

四、 与 null 相关的函数

ISNULL ISNULL 检测表达式是否为 NULL ，如果是的话替换 NULL 值为另外一个值 COALESCE COALESCE函数返回指定表达式列表的第一个非 NULL 值 NULLIF 当指定的两个表达式有相同值的时候 NULLIF 返回 NULL值，否则返回第一个表达式的值

针对Sqlserver与Oracle中null值的不同:

一、在SQL Server中与oracle正相反，NULL值会被认为是一个无穷小的值，所以如果按照升序排列的话，则会被排在最前面

二、SQL Server和Oracle中对插入数据值包含空的处理有所差异，在SQL Server中，我们可以把表字段设计为非空，但我们仍然可以通过下面语句执行插入操作：

INSERT INTO Table (TestCol) VALUES(‘’)

其中的TestCol字段，在设计的时候，已经被设计为NOT NULL在sql server中，null和空格是不同的，也就是说，上面的语句插入的是一个空，但并不是NULL，只有当我们的插入语句中没有该字段的时候，才会被认为违反非空的条件约束，如果把NULL翻译成“空”的话，可能就会很容易搞混了。此外，如果我们的字段是INT类型的话，如果我们插入空的话，会得到一个0，也就是说，Sql server会自动帮我们处理对空格的转化。

但是在Oracle中，这个便利便不存在了，必须严格按照规则来进行插入，也就是说，我们再想视图通过插入空来满足NOT NULL的设计约束，已经不能成功了，必须插入实实在在的内容才能符合NOT NULL的约束。

注：这里没有将举例copy过来，例子都是比较显而易见的。了解了null的基本操作之后，就好尴尬啊。如果字段值中存储了null值在oracle中我们有想通过in的方式来查询出来这个null值就基本不可能了。只有将字段进行函数处理nvl(column_name,’_NA_’) in(xxxx, ’_NA_’)来实现了，或者column_name in (xxxx) or column_name is null。

不过针对这些存储了null的字段并且在查询过程中使用了in，多字段的查询针对性的建立索引也是没有什么用的。null值也会给索引增加负担，报表中的多条件查询也不会同时都走上索引。只能乖乖的使用nvl来进行转化后的筛选条件下的全扫描。

sql server 中order by 中关于null值处理

sqlserver 认为 null 最小。 
升序排列：null 值默认排在最前。 
要想排后面，则：order by case when col is null then 1 else 0 end ,col 
降序排列：null 值默认排在最后。 
要想排在前面，则：order by case when col is null then 0 else 1 end , col desc

1、on 、where、having中把unknown当作FALSE处理，使用筛选器为unknown的行会排除在外， 
而check约束中的unknown的值被当做true，假设一个check约束中要求salary大于0，插入salary为null 
的行可以被接受 NUll > 0 的结果为unknown 
2、unique约束、排序、分组认为两个NULL是相等的 
如果表的一列被定义为unique约束，将不能插入两个为NULL值得行 
group by 把所有null分为一组 
order by 把所有的null值排列在一起

select distinct top col from t order by col 
先执行distinct –》order by –》top