当字段具有NULL值时，SQL WHERE子句不返回行 [英] SQL WHERE clause not returning rows when field has NULL value

问题描述

好的，所以我知道这个问题:

Ok, so I'm aware of this issue:

当SET ANSI_NULLS设置为ON时，对空值的所有比较均得出UNKNOWN

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN

• where子句中的SQL和NULL值
• SQL Server返回不等于<>等于值和NULL的行

但是，我正在尝试查询 DataTable .

However, I am trying to query a DataTable.

我可以添加到查询中

OR col_1 IS NULL OR col_2 IS NULL

每列，但我的表有47列，并且我正在构建动态SQL(字符串连接)，这样做似乎很痛苦.还有其他解决方案吗?

for every column, but my table has 47 columns, and I'm building dynamic SQL (string concatenation), and it just seems like a pain to do that. Is there another solution?

我要带回所有在 WHERE 比较中具有 NULL 值的行.

I was to bring back all the rows that have NULL values in the WHERE comparison.

更新

给我一​​些问题的查询示例:

Example of query that gave me problems:

string query = col_1 not in ('Dorothy', 'Blanche') and col_2 not in ('Zborna', 'Devereaux')
grid.DataContext = dataTable.Select(query).CopyToDataTable();

(如果/当 col_1 = null 和/或 col_2 = null 时，不检索行)

(didn't retrieve rows if/when col_1 = null and/or col_2 = null)

推荐答案

所以你的意思是类似的(例如2列示例)

So you mean something like (example with 2 columns)

WHERE (col1 = 'abc' or col1 is null)
  AND (col2 = 3 or col2 is null)

但是您想始终包含空值吗?这应该工作

But you want to include the nulls always? This should work

WHERE isnull(col1,'abc') = 'abc'
  AND isnull(col2, 3) = 3

这篇关于当字段具有NULL值时，SQL WHERE子句不返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！