【ORACLE】特殊的NULL
时间：2021-07-01 10:21:17
帮助过：14人阅读
null
Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.
当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的既然是未知的，就有无数种的可能性。
因此， NULL 并不是一个确定的值
-- 判断一个列为空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NULL;
-- 判断一个列非空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NOT NULL;
测试：
--NULL=NULL么： NULL is not equal to 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
SELECT CASE
WHEN ‘‘ = NULL THEN
‘空串 is equal to NULL‘
ELSE
‘空串 is not equal to NULL‘
END "空串=NULL?"
FROM DUAL;
--空格=NULL么： 空格 is not equal to NULL
SELECT CASE
WHEN ‘ ‘ = NULL THEN
‘空格 is equal to NULL‘
ELSE
‘空格 is not equal to NULL‘
END "空格=NULL?"
FROM DUAL;
--‘‘ 是NULL么: ‘‘ is NULL
SELECT CASE
WHEN ‘‘ IS NULL THEN
‘‘‘‘‘ is NULL‘
ELSE
‘‘‘‘‘ is not NULL‘
END "‘‘ is NULL?"
FROM DUAL;
--空串=空串么：‘‘ is not equal to ‘‘
SELECT CASE
WHEN ‘‘ = ‘‘ THEN
‘‘‘‘‘ is equal to ‘‘‘‘‘
ELSE
‘‘‘‘‘ is not equal to ‘‘‘‘‘
END "‘‘‘‘=‘‘‘‘?"
FROM DUAL;
-- 将null替换成一个值
select 1 from dual where nvl(null,0)=nvl(null,0);
NULL 排序问题
方法 1 将null替换成一个值 ,是用nvl 函数
select 1 from dual where nvl(null,0)=nvl(null,0);
方法2 是用NULLS LAST，NULLS FIRST处理
SELECT c.modifydt FROM customeren c ORDER BY c.modifydt NULLS LAST;
SELECT c.modifydt FROM customeren c ORDER BY c.modifydt NULLS FIRST;
【ORACLE】特殊的NULL
标签：
