验证列是否具有空值

Question

哪种SQL查询方式能更快地验证特定列是否有空值，为什么？

1) SELECT * FROM TABLE1 WHERE COL1 IS NULL

执行此查询，然后检查是否能读取任何记录。如果可以，则存在空值。

2) SELECT COUNT(COL1) FROM TABLE1 WHERE COL1 IS NULL

读取返回的计数以确定是否存在任何空记录。

适用于Oracle10g和SQLServer2005。

- MOZILLA

Answer 1

Count(columnName)永远不会统计NULL值，当您指定列名时，count跳过NULLS并计算非NULLS，而使用*时会计算NULLS。

运行此命令。

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

SELECT count(*) FROM testnulls WHERE ID IS NULL --1

SELECT count(ID) FROM testnulls WHERE ID IS NULL --0

我建议使用exists，因为它是一个布尔运算符，并且会在第一次出现NULL时停止。

IF EXISTS (SELECT 1 FROM testnulls WHERE ID IS NULL)
PRINT 'YES'
ELSE
PRINT 'NO'

- SQLMenace

Answer 2

在kquinn的回答基础上，对于Oracle来说，应该是这样的：

SELECT COL1 FROM TABLE1 WHERE COL1 IS NULL AND ROWNUM = 1;

那样，DBMS只需在给出答案之前读取一行；然而，该语句是具有误导性的。它必须阅读所有行，直到找到缺少列值的行。然后它可以停止并返回该行。如果没有这样的行，它将读取整个表。

因此，在COL1上建立索引可能是满足查询的最佳方法，使查询速度更快。但是，仅指定COL1不会对Oracle产生太大影响，因为（常规B-Tree）索引无法用于查找NULL值。如果您有兴趣稍后识别行，则可能仍希望选择更多列（例如主键值）。

- Thilo

---

嗯，在我选择的PostgreSQL数据库中，B-Tree索引（默认）确实存储有关NULL值的信息，并且可以用于满足查询中的IS NULL条件；我同意您的观点，如果无论如何都不能使用索引，使索引覆盖查询也不会有太大帮助。您需要了解SQL Server索引如何处理NULL，并查看它是否像Postgres或Oracle一样运行。 - kquinn

Answer 3

我不清楚Oracle，但对于SQL Server来说，这个选项可能是所有选项中最快的：

SELECT TOP 1 COL1 FROM TABLE1 WHERE COL1 IS NULL;

这样一来，数据库管理系统在给出答案之前只需要读取单个行；其他选项则需要读取所有非空行。而且我指定了 COL1 而不是 *，所以可能可以通过在 COL1 上建立索引来满足查询，从而使查询速度更快。

- kquinn

---

仅仅是挑刺，数据库可能仍然需要读取多行数据。它只能在最终找到符合条件的行时停止。在到达符合条件的行之前，它仍然需要读取任何未通过条件的行。此外，使用 EXISTS 也会带来同样的优势，我认为这也符合 ANSI 标准。 - Tom H

Answer 4

多种解决方案（列包含一些NULL值 | 列全部为NULL值 * 测试单个列 | 测试多个列并获得表格结果）

如果您需要测试多个列，可以使用以下方法：

Column_1 Column_2 Column_3
-------- -------- --------
1        2        NULL
1        NULL     NULL
5        6        NULL

首先，测试 NULL 值并计数：

select 
    sum(case when Column_1 is null then 1 else 0 end) as Column_1, 
    sum(case when Column_2 is null then 1 else 0 end) as Column_2, 
    sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable 

返回 NULL 的数量：

Column_1  Column_2  Column_3
0         1         3

当结果为0时，表示没有NULL。

第二步，让我们统计非NULL值：

select 
    sum(case when Column_1 is null then 0 else 1 end) as Column_1, 
    sum(case when Column_2 is null then 0 else 1 end) as Column_2, 
    sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable

但因为我们在统计非 NULL 值，所以可以简化为：

select 
    count(Column_1) as Column_1, 
    count(Column_2) as Column_2, 
    count(Column_3) as Column_3,
from TestTable

任何一种都可以产生：

Column_1  Column_2  Column_3
3         2         0

当结果为0时，该列完全由NULL组成。

最后，如果您只需要检查特定列，则使用TOP 1会更快，因为它应该在第一个命中时停止。然后，您可以选择使用count(*)来给出布尔风格的结果：

select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a

0 = 没有 NULL，1 = 至少有一个 NULL

select count(*) from (select top 1 'There is at least one non-NULL' AS note from TestTable where Column_3 is not NULL) a

0 = 它们都是 NULL，1 = 至少有一个非 NULL

希望这可以帮到你。

- jwolf