Oracle 空值计算总结(踩过不少坑)

CREATE TABLE T1 (ID INT ,ID1 INT);
INSERT INTO T1 VALUES(1,NULL);
INSERT INTO T1 VALUES(NULL,NULL);
INSERT INTO T1 VALUES(2,2);
COMMIT;

--实际上有三条记录,包括两个字段都为空的记录
admin@ORCL> SELECT * FROM T1;

        ID        ID1
---------- ----------
         1         NULL
         2          2
        NULL       NULL


admin@ORCL> SELECT COUNT(*) FROM T1;

  COUNT(*)
----------
         3

admin@ORCL> SELECT COUNT(ID) FROM T1;

 COUNT(ID)
----------
         2

admin@ORCL> SELECT COUNT(ID1) FROM T1;

COUNT(ID1)
----------
         1
--空值的SUM测试
admin@ORCL> SELECT SUM(ID) FROM T1;--SUM值不考虑空值

   SUM(ID)
----------
         3
admin@ORCL> SELECT AVG(ID) FROM T1;--AVG只除以了非为空的记录条数

   AVG(ID)
----------
       1.5

admin@ORCL> SELECT SUM(ID1) FROM T1;

  SUM(ID1)
----------
         2
         
admin@ORCL> SELECT AVG(ID1) FROM T1;--avg只除以了非空的记录条数

  AVG(ID1)
----------
         2
--相加测试，NULL+NOT NULL VALUE = NULL
admin@ORCL> SELECT ID+ID1 FROM T1;

    ID+ID1
----------
        NULL
         4
--MAX与MIN测试，最大值和最小值也不包含空
admin@ORCL> select max(ID) from t1;

   MAX(ID)
----------
         2

admin@ORCL> select min(ID) from t1;

   MIN(ID)
----------
         1
--排序测试

select * from t1 order by id;

admin@ORCL> select * from t1 order by id;--默认是升序,可以看出NULL 默认最大

        ID        ID1
---------- ----------
         1         NULL
         2          2
        NULL       NULL

admin@ORCL> select * from t1 order by id1;

        ID        ID1
---------- ----------
         2          2
        NULL       NULL
         1         NULL
--加上 NULLS first，将空值设为第一位

admin@ORCL> select * from t1 order by id nulls first;

        ID        ID1
---------- ----------

         1
         2          2

--distinct会包含null
admin@ORCL> select distinct id from t1;

        ID
----------
         1

         2



--1.NULL值对IN的查询无太影响
SELECT *
  FROM PRODUCT_BASE
 WHERE PRODUCT_CODE IN (SELECT 'C10104107000015'
                          FROM DUAL
                        UNION ALL
                        SELECT NULL FROM DUAL);
--2.NULL值对NOT IN的查询有影响,会导致全部数据查询不出来
SELECT *
  FROM PRODUCT_BASE
 WHERE PRODUCT_CODE IN (SELECT 'C10104107000015'
                          FROM DUAL
                        UNION ALL
                        SELECT NULL FROM DUAL);
--结论 in与not in 会跟括号里面的值进行比较是否相等从而得出判断结果，而在oracle中null
进行比较都是返回false.
--3.EXIST值对NULL的查询无影响
SELECT *
  FROM PRODUCT_BASE
 WHERE EXISTS (SELECT 1
          FROM (SELECT 'C10104107000015' AS ID
                  FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) T
         WHERE T.ID = PRODUCT_CODE);
--3.NOT EXIST值对NULL的查询无影响
SELECT *
  FROM PRODUCT_BASE
 WHERE NOT EXISTS (SELECT 1
          FROM (SELECT 'C10104107000015' AS ID
                  FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) T
         WHERE T.ID = PRODUCT_CODE);
         
--结论 
--exists与not exists相当于一种逻辑判断，exists 的本质就是返回一个布尔值,
--exists测试关联子查询是否有数据返回，如果有至少一行返回的话则exists判断为真返回true，
--not exists判断关联子查询是否没有数据返回， 如果没有数据返回则判断为真，返回true。

以下为踩过的坑。。。。。
1.merge 关联时, null与null关联不上, 导致数据都被insert进去,而非更新。
如下示例，A表的xx1与b表的xx1都为null，关联不上。
merge into  A
using（select * from B） on a.id = B.id and a.xx1 = b.xx1 
when matched then
when not matched then insert

2.merge关联后，两个字段相加null+数字仍然为空。具体场景为计算累加值，而上日无此维度累加值且未设置默认值为0，导致计算结果为null。
merge into A
using (select * from b) b on a.id = b.id
when matched
then update set A.amount = A.amount + B.amount(null)

3.数据清理时, 
通过create table 再drop 原表rename的方式清理，取日期大于某个值的数据(业务上要求要包括null记录)。
create table T  select * from source where source.date >= sysdate - 90;