SQL 检查是否有任何列不是 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8715310/
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
Check if any column is NOT NULL
提问by bitsmuggler
I need to check whether a column is NOT NULL in my SQL statement.
我需要检查我的 SQL 语句中的列是否为 NOT NULL。
My SQL query:
我的 SQL 查询：
select column_a, column_b, column_c, column_d, column_x
from myTable
I've a lot of columns in my select. So I've got a performance issue, If I would do the following:
我的选择中有很多列。所以我有一个性能问题，如果我会做以下事情：
select column_a, column_b, column_c, column_d, column_x
from myTable
where column_a is not null or column_b is not null or column_c is not null
or column_x is not null
Is there another (better) way to check if there are any columns that are NOT NULL?
是否有另一种（更好的）方法来检查是否有任何非空的列？
回答by RedFilter
You can use COALESCEfor this. COALESCEreturns the first non-null value, if any. This will likely not perform any better, but is much more readable.
您可以COALESCE为此使用。COALESCE返回第一个非空值（如果有）。这可能不会表现得更好，但更具可读性。
Example:
例子：
where coalesce(column_a, column_b, column_c, column_x) is not null
Depending on the cardinality of your data, you may be able to add indexes to help performance.
根据数据的基数，您可以添加索引以提高性能。
Another possibility is to use persisted computed column that tells you whether all four columns are NULL or not.
另一种可能性是使用持久计算列来告诉您所有四列是否都是 NULL。
回答by RQDQ
One way to attack this might be to add an additional bit column that keeps track of whether there are any values or not.
解决这个问题的一种方法可能是添加一个额外的位列来跟踪是否有任何值。
Pros
优点
- Can be implemented with triggers so you don't need to change the rest of your code
- Doesn't require scanning the other columns
- That column can be indexed
- 可以使用触发器实现，因此您无需更改其余代码
- 不需要扫描其他列
- 该列可以被索引
Cons
缺点
- Your data would be de-normalized
- More complicated / more maintenance
- More storage space for the additional column
- 您的数据将被非规范化
- 更复杂/更多维护
- 附加列的更多存储空间
Whether the pros outweigh the cons depend on how much of a performance hit you're taking by looking at the other columns. Profile it before committing!
优点是否大于缺点取决于您通过查看其他列对性能的影响有多大。在提交之前对其进行配置！
回答by Solomon Rutzky
I generally like @RedFilter's suggestion of COALESCE, but another solution might be to use the CHECKSUM() function. Of course, the value of the checksum for all NULLs depends on the columns and datatypes so you would need to first run a query to get that value. Something like:
我通常喜欢@RedFilter 对 COALESCE 的建议，但另一种解决方案可能是使用 CHECKSUM() 函数。当然，所有 NULL 的校验和的值取决于列和数据类型，因此您需要先运行查询以获取该值。就像是：
select CHECKSUM(*) AS [All_NULL_Value]
from myTable
where column_a is null
AND column_b is null
AND column_c is null
AND column_d is null
AND column_x is null
Then you can do this:
然后你可以这样做：
select column_a, column_b, column_c, column_d, column_x
from myTable
where CHECKSUM(*) <> {All_NULL_Value_obtained_above}
I am not sure if this performs better or worse than the COALESCE idea but might be worth a try.
我不确定这是否比 COALESCE 的想法表现更好或更差，但可能值得一试。
回答by Juozas
Answer accepted 5 years ago, but, as said Brad, by question title coalesce is wrong approach. If in some cases you really need to check or ANY parameter is null, you can use this:
答案在 5 年前接受，但正如布拉德所说，通过问题标题合并是错误的方法。如果在某些情况下你真的需要检查或任何参数为空，你可以使用这个：
where convert(binary, column_a) + convert(binary, column_b) + convert(binary, column_c), + convert(binary, column_k) is null
