oracle 验证列是否具有空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/771265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Validate if a column has a null value
提问by MOZILLA
Which SQL would be faster to validate if a particular column has a null value or not, why?
哪个 SQL 可以更快地验证特定列是否具有空值，为什么？
1) SELECT * FROM TABLE1 WHERE COL1 IS NULL
1) SELECT * FROM TABLE1 WHERE COL1 为 NULL
Execute this query and then check if you are able to read any records. If yes there are null values.
执行此查询，然后检查您是否能够读取任何记录。如果是，则有空值。
2) SELECT COUNT(COL1) FROM TABLE1 WHERE COL1 IS NULL
2) SELECT COUNT(COL1) FROM TABLE1 where COL1 is NULL
Read the count which is returned to determine if there are any null records
读取返回的计数以确定是否有任何空记录
Working with Oracle10g and SQLServer2005.
使用 Oracle10g 和 SQLServer2005。
回答by SQLMenace
Count(columnName) will NEVER count NULL values, count skips NULLS when you specify a column name and does count NULLS when you use *
Count(columnName) 永远不会计算 NULL 值，当您指定列名时，count 会跳过 NULLS，当您使用 * 时会计算 NULLS
run this
运行这个
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
I would use exists instead since it is a boolean operation and will stop at the first occurance of NULL
我会改用exists，因为它是一个布尔运算，并且会在第一次出现NULL时停止
IF EXISTS (SELECT 1 FROM testnulls WHERE ID IS NULL)
PRINT 'YES'
ELSE
PRINT 'NO'
回答by Thilo
Building on kquinn's answer, in Oracle that would be
基于 kquinn 的答案，在 Oracle 中，这将是
SELECT COL1 FROM TABLE1 WHERE COL1 IS NULL AND ROWNUM = 1;
That way the DBMS only has to read a single row before giving you your answer;
这样 DBMS 在给你答案之前只需要读取一行；
That statement is misleading, however. It has to read all rows until it finds one with the missing column value. Then it can stop and return that row.
然而，这种说法具有误导性。它必须读取所有行，直到找到具有缺失列值的行。然后它可以停止并返回该行。
If there is no such row, it will read the whole table.
如果没有这样的行，它将读取整个表。
so it might be possible to satisfy the query with an index on COL1, making the query faster still.
因此可能可以使用 COL1 上的索引来满足查询，从而使查询速度更快。
Specifying only COL1 will not have too much impact, at least on Oracle, where (regular B-Tree) indices cannot be used to find NULL values.
仅指定 COL1 不会产生太大影响，至少在 Oracle 中，其中（常规 B 树）索引不能用于查找 NULL 值。
You may want to select more columns anyway (such as the primary key value) if you are interested in identifiying the row later.
如果您有兴趣稍后识别该行，则无论如何您可能想要选择更多列（例如主键值）。
回答by kquinn
I don't know about Oracle, but for SQL Server this option is probably going to be fastest of all:
我不了解 Oracle，但对于 SQL Server，此选项可能是最快的：
SELECT TOP 1 COL1 FROM TABLE1 WHERE COL1 IS NULL;
That way the DBMS only has to read a single row before giving you your answer; the other options have to read allnon-null rows. And I've specified COL1instead of *, so it might be possible to satisfy the query with an index on COL1, making the query faster still.
这样 DBMS 在给你答案之前只需要读取一行；其他选项必须读取所有非空行。并且我指定了COL1而不是*，因此可能可以通过索引 on 来满足查询COL1，从而使查询更快。
回答by jwolf
Multiple Solutions (Column Contains Some NULLs | Column is All NULLs * Test Single Column | Test Multiple Columns with Tabular Results)
多个解决方案（列包含一些 NULL | 列都是 NULL * 测试单列 | 用表格结果测试多列）
If you need to test multiple columns, you could use the following:
如果您需要测试多个列，您可以使用以下内容：
Column_1 Column_2 Column_3
-------- -------- --------
1 2 NULL
1 NULL NULL
5 6 NULL
First, test for NULLs and count them:
首先，测试 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
Yields a count of NULLs:
产生 NULL 计数：
Column_1 Column_2 Column_3
0 1 3
Where the result is 0, there are no NULLs.
如果结果为 0，则没有 NULL。
Second, let's count the non-NULLs:
其次，让我们计算一下非 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
...But because we're counting non-NULLs here, this can be simplified to:
...但因为我们在这里计算非 NULL，这可以简化为：
select
count(Column_1) as Column_1,
count(Column_2) as Column_2,
count(Column_3) as Column_3,
from TestTable
Either one yields:
任一产生：
Column_1 Column_2 Column_3
3 2 0
Where the result is 0, the column is entirely made up of NULLs.
如果结果为 0，则该列完全由 NULL 组成。
Lastly, if you only need to check a specific column, then TOP 1 is quicker because it should stop at the first hit. You can then optionally use count(*) to give a boolean-style result:
最后，如果您只需要检查特定的列，那么 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 = There are no NULLs, 1 = There is at least one NULL
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 = They are all NULL, 1 = There is at least one non-NULL
0 = 它们都是 NULL，1 = 至少有一个非 NULL
I hope this helps.
我希望这有帮助。
