in,not in,like,not like,=,!=与null的恩怨情仇

1.情景展示

当字段值为null时，会给我们的查询带来不小的麻烦，今天就来聊一聊：

in(),not in(),like,not like,=,!=或<>在字段值为null时，出现的各种问题。

2.具体分析

以oracle进行举例分析。

现有一张BASE_ORG_INFO表

如上图所示，ORGFULLNAME字段值为NULL的有210条数据，共有112123条数据。

in()

SELECT count(1) FROM base_org_info t where t.orgfullname in ('河南省新乡市牧野区工业园区');

共有一条数据。 

SELECT count(1) FROM base_org_info t where t.orgfullname in (null);

 

已知，ORGFULLNAME字段值为NULL的有210条数据，现在使用in()进行统计，结果却为0。

SELECT count(1) FROM base_org_info t where t.orgfullname in ('河南省新乡市牧野区工业园区', null);

即使，我们把null放到追加的后面也不行。 

事实证明：当使用in()作为查询条件时，null值不会被统计在内，即使将null强行追加在后面。

如果非要统计，我们可以这样做：in()+is null。

SELECT SUM(B.HJ) TOTAL
  FROM (SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME IN ('河南省新乡市牧野区工业园区')
        UNION ALL
        SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME IS NULL) B

not in()

统计orgfullname不是‘河南省新乡市牧野区工业园区’的数据。

执行下列SQL

SELECT count(1) FROM base_org_info t where t.orgfullname not in ('河南省新乡市牧野区工业园区');

已知，该表总条数为112123，查询结果应为：112123-1=112122条，实际却为：111912。

又知，orgfullname字段值为null的，共有210条数据。210+111912=112122。

我们可以看到：

not in()是不会将null值统计在内的。

结论1：我们当我们使用not in()函数作为查询条件时， 如果not in()里面不包含null值，null值不会统计在内。

替代SQL

那如果我们强行将null加上，会发生什么呢？

统计orgfullname既不是‘河南省新乡市牧野区工业园区’又不是orgfullname不为null的数据

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME NOT IN ('河南省新乡市牧野区工业园区', null);

好家伙，一旦把null加上，直接没数据了。 

实际上，应为：112123-1-210=111912。

也许你会说，这样的操作毫无意义，那继续往下走。

统计orgfullname非空的数据

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME NOT IN
       (SELECT ORGFULLNAME FROM BASE_ORG_INFO WHERE ORGFULLNAME IS NULL)

 

我们可以看到，只要not in()函数当中包含null值，这就变成了一个无效SQL，统计结果必然是错误的。

结论2：我们当我们使用not in()函数作为查询条件时， 如果not in()里面包含null值，那这SQL将毫无意义。

因为null值不等于任何非空值，所以，任何值与null做比较，都将返回false。

更多介绍，见!=。

替代SQL

like

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME LIKE '河南省新乡市牧野区工业园区'

我们可以看到：

当like作为查询条件时，null值是不会被统计在内的。 

not like

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME NOT LIKE '河南省新乡市牧野区工业园区';

它的查询结果与not in('河南省新乡市牧野区工业园区')的结果一致。

结论：我们当我们使用not like作为查询条件时，null值将不会被统计在内。

替代SQL

SELECT SUM(B.HJ) TOTAL
  FROM (SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME NOT LIKE '河南省新乡市牧野区工业园区'
        UNION ALL
        SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME IS NULL) B

 

=

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME = '河南省新乡市牧野区工业园区';

SELECT COUNT(1) FROM BASE_ORG_INFO T WHERE T.ORGFULLNAME = NULL

=null这种用法应该是不存在的，起码我没这样用过。

替代SQL

 

!=或<>

SELECT COUNT(1)
  FROM BASE_ORG_INFO T
 WHERE T.ORGFULLNAME <> '河南省新乡市牧野区工业园区';

此查询结果与not in('河南省新乡市牧野区工业园区')、not like('河南省新乡市牧野区工业园区')的结果一致。

结论：我们当我们使用<>或!=作为查询条件时，null值将不会被统计在内。

替代SQL

SELECT SUM(B.HJ) TOTAL
  FROM (SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME != '河南省新乡市牧野区工业园区'
        UNION ALL
        SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T
         WHERE T.ORGFULLNAME IS NULL) B

SELECT COUNT(1) FROM BASE_ORG_INFO T WHERE T.ORGFULLNAME <> NULL;

 

3.解决方案

exists()

SELECT COUNT(1)
  FROM BASE_ORG_INFO T1
 WHERE EXISTS (SELECT 1
          FROM BASE_ORG_INFO T2
         WHERE T2.ORGFULLNAME <> '河南省新乡市牧野区工业园区'
           AND T2.ORGFULLNAME = T1.ORGFULLNAME
        UNION ALL
        SELECT 1
          FROM BASE_ORG_INFO T2
         WHERE T2.ORGFULLNAME IS NULL
           AND T2.ORGFULLNAME = T1.ORGFULLNAME);

它的执行结果，是不是感觉匪夷所思？

通过执行此SQL，发现：结果为0，为什么会这样？事实上，此SQL存在逻辑错误。

还是那句话，任何与null进行做对比的都将返回false。

出现问题的不是exists()函数，而是：

WHERE T2.ORGFULLNAME IS NULL
           AND T2.ORGFULLNAME = T1.ORGFULLNAME

既然第一个条件已经将orgfullname已经筛选出来了，后面第二个条件，又拿null和字段多对比，结果自然是没有数据。

替代SQL

SELECT SUM(B.HJ) TOTAL
  FROM (SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T1
         WHERE EXISTS (SELECT 1
                  FROM BASE_ORG_INFO T2
                 WHERE T2.ORGFULLNAME <> '河南省新乡市牧野区工业园区'
                   AND T2.ORGFULLNAME = T1.ORGFULLNAME)
        UNION ALL
        SELECT COUNT(1) HJ
          FROM BASE_ORG_INFO T1
         WHERE T1.ORGFULLNAME IS NULL) B;

这样一来，使用exists()也必须考虑null值的问题，这并不符合exists的使用初衷（exists只考虑是否有行返回，无需关心值是否为null）。

所以，并不推荐这样使用，我们可以使用not exists()轻松搞定。

not exists()

SELECT COUNT(1)
  FROM BASE_ORG_INFO T1
 WHERE NOT EXISTS (SELECT 1
          FROM BASE_ORG_INFO T2
         WHERE T2.ORGFULLNAME = '河南省新乡市牧野区工业园区'
           AND T2.ORGFULLNAME = T1.ORGFULLNAME);

已知，总条数为112123，orgfullname='河南省新乡市牧野区工业园区'有1条。

由此可以判断出，此SQL是正确的。

说明：

EXISTS() 和NOT EXISTS() 是 SQL 中用于子查询的一个条件表达式。它通常用于检查子查询是否返回任何行。

如果子查询返回的结果集中包含 NULL 值，这通常不会影响 EXISTS 的判断。

EXISTS 只关心是否有行返回，而不关心这些行的具体内容或是否包含 NULL 值。

4.总结

not in(),not like,!=在作为查询条件进行数据比对时，其执行结果，并不包含null的数据。

这其实也正常，因为null不能和任何值作比较，既然没有比较的意义，那not in(), not like, !=的执行结果不包含null值所在行的记录才符合SQL的要求。

但在实际需求中，我们往往是需要这些null数据的，这就要求我们要记得在原有SQL的基础上追加查询值为null的记录。

另外，count(列名)也不包含列名值为null的行的记录。

什么时候用in()、not in()，什么时候用exists()、not exists()？

理论上，外表大、子表小用in()，外表小、字表大用exists()。

但实际上要分情况讨论：

当我们确定子查询的where条件所使用的表字段不包含null值时，我们可以使用in()或者not in()，否则，使用exists()或者not exists()；

当我们in()里面的值确定时，我们可以使用in，如：in(1,2,3)。

posted @ 2024-03-20 15:00  Marydon  阅读(19)  评论(0)  编辑  收藏  举报