c# - 为什么在从表中选择值时排除 NULL 值

Question

我写了一个 sql 查询来获取所有记录但不是特定的。

我的表有一个类型为 INT 的 column3 并且也可以有一些空值。但是以下查询忽略了 column3 中具有 NULL 的记录。

表 1:

ID | Column1 | Column2 | Column3
1     xyz        xyz       200
2     xyz        xyz       201
3     xyz        xyz       NULL
4     xyz        xyz       NULL
5     xyz        xyz       201

SQL查询:

SELECT 
    [ID], 
    [Column1], [Column2], [Column3] // (int, null)
FROM 
    [Table1] 
WHERE
    Column3 != 201

LINQ 查询:

from tb in _entities.Table1
where tb.Column3 != 201

因为两个 NULL 不能相等，即 NULL = NULL 总是 False。为什么上述查询排除了具有空值的记录。仅返回第一条记录。

Answer 1

您需要explicitly指定您想要 NULL 值:

from tb in _entities.Table1
where tb.Column3 == null || tb.Column3 != 201

When null values are present in data, logical and comparison operatorscan potentially return a third result of UNKNOWN instead of just TRUEor FALSE. This need for three-valued logic is a source of manyapplication errors.

Transact-SQL also offers an extension for null processing. If theoption ANSI_NULLS is set to OFF, comparisons between nulls, such asNULL = NULL, evaluate to TRUE. Comparisons between NULL and any datavalue evaluate to FALSE.