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。