那些年素素在数据库使用过程中踩过的坑-1

1 NULL和NULL关联返回的不是TURE

SELECT COUNT(1)
FROM 
(
SELECT NULL AS X  FROM DUAL
)A1,
(
SELECT NULL AS Y  FROM DUAL
)A2
WHERE A1.X = A2.Y;

2 NULL和’'都是空值

SELECT COUNT(1) ,  --0
       COUNT(X)    --0
FROM 
(
SELECT NULL AS X  FROM DUAL
UNION ALL
SELECT '' AS X FROM DUAL
)

3 NULL和’’ 都可以用IS NULL来进行判断

SELECT COUNT(1)  --2 
FROM 
(
SELECT NULL AS X  FROM DUAL
UNION ALL
SELECT '' AS X FROM DUAL
) WHERE X IS NULL

4 NULL和’’ 都 不能用等于 ''来进行判断

SELECT COUNT(1)  --0
FROM 
(
SELECT NULL AS X  FROM DUAL
UNION ALL
SELECT '' AS X FROM DUAL
) WHERE X = ''

4 NULL和’’ 都 不能用不等于 ''来进行判断

SELECT COUNT(1)  --0
FROM 
(
SELECT NULL AS X  FROM DUAL
UNION ALL
SELECT '' AS X FROM DUAL
) WHERE X <>''

5 NULL 的长度为NULL

-- NULL
select length(X)
from (SELECT NULL AS X  FROM DUAL) A1

6 ''的长度为NULL

select length(X)
from (SELECT '' AS X  FROM DUAL) A1;

7 ''和NULL都返回NULL
WITH A1 AS (
select '' as y from dual 
union all 
select null as y from dual
)select count(1), count(*) from a1