1.Oracle 中对null的描述
null
Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.
NULL 是数据库中特有的数据类型,当一条记录的某个列为 NULL ,则表示这个列的值是未知的、是不确定的既然是未知的,就有无数种的可能性。因此, NULL 并不是一个确定的值
2.null的判断
-- 判断一个列为空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NULL;
-- 判断一个列非空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NOT NULL;
3.null 的比较
1. NULL=NULL么?
SELECT CASE
WHEN NULL = NULL THEN
'NULL is equal to NULL'
ELSE
'NULL is not equal to NULL'
END "NULL=NULL?"
FROM DUAL;
--结果NULL is not equal to NULL
2. 空串=NULL么?
SELECT CASE
WHEN '' = NULL THEN
'空串 is equal to NULL'
ELSE
'空串 is not equal to NULL'
END "空串=NULL?"
FROM DUAL;
--结果空串 is not equal to NULL
3.空格=NULL么?
SELECT CASE
WHEN ' ' = NULL THEN
'空格 is equal to NULL'
ELSE
'空格 is not equal to NULL'
END "空格=NULL?"
FROM DUAL;
--结果空格 is not equal to NULL
4.空串'' 是NULL么
SELECT CASE
WHEN '' IS NULL THEN
''''' is NULL'
ELSE
''''' is not NULL'
END "'' is NULL?"
FROM DUAL;
--结果 '' is NULL
5.空串=空串么
SELECT CASE
WHEN '' = '' THEN
''''' is equal to '''''
ELSE
''''' is not equal to '''''
END "''''=''''?"
FROM DUAL;
--结果'' is not equal to ''
4.null替换
--nvl函数将null替换成一个值
select 1 from dual where nvl(null,0)=nvl(null,0);
5.null排序
--方法 1 将null替换成一个值 ,是用nvl 函数
select 1 from dual where nvl(null,0)=nvl(null,0);
--方法2 是用NULLS LAST,NULLS FIRST处理
SELECT c.modifydt FROM producten c ORDER BY c.modifydt NULLS LAST;
SELECT c.modifydt FROM producten c ORDER BY c.modifydt NULLS FIRST;