I have a table that has a column of values that can be rowTypeID = (1,2,3, or null) . I would like to write a query that returns any row that doesn't have a value of 3 in it. In this example I want all NULL rows along with all 1,2 rows I just don't want rows with the value of 3
我有一个表有一列值可以是rowTypeID =（1,2,3或null）。我想编写一个查询，返回任何没有值为3的行。在这个例子中，我想要所有NULL行以及所有1,2行我只是不想要值为3的行
Set ANSI null ON is currently set for the database.
当前为数据库设置了ANSI null ON。
I'm curious as to why I can't write
我很好奇为什么我不能写
select * from myTable where myCol <> 3
This query will not return any rows that have NULL in the myCol column
此查询不会返回myCol列中具有NULL的任何行
I have to write
我要写
select * from my Table where myCol <> 3 or myCol Is NULL
Do I always have to include the IS NULL or can I set it up so a where clause myCol <>3 will return rows that have Null as value for my Col
我是否总是必须包含IS NULL或者我可以设置它以使where子句myCol <> 3将返回具有Null作为我的Col的值的行
5 个解决方案
#1
15
I think your approach is fine:
我认为你的方法很好：
SELECT *
FROM MyTable
WHERE myCol <> 3 OR myCol IS NULL
Since you are asking for alternatives, another way to do it is to make your column NOT NULL and store another (otherwised unused) value in the database instead of NULL - for example -1. Then the expression myCol <> 3 will match your fake-NULL just as it would with any other value.
由于您要求替代方法，另一种方法是使您的列NOT NULL并在数据库中存储另一个（其他未使用的）值而不是NULL - 例如-1。然后表达式myCol <> 3将匹配您的假NULL，就像它与任何其他值一样。
SELECT *
FROM MyTable
WHERE myCol <> 3
However in general I would recommend not to use this approach. The way you are already doing it is the right way.
但总的来说，我建议不要使用这种方法。你正在做的方式是正确的方式。
Also it might be worth mentioning that several other databases support IS DISTINCT FROM which does exactly what you want:
另外值得一提的是，其他几个数据库支持IS DISTINCT FROM，它可以完全满足您的需求：
SELECT *
FROM MyTable
WHERE myCol IS DISTINCT FROM 3
MySQL has the NULL-safe equal which can also be used for this purpose:
MySQL具有NULL安全相等，也可用于此目的：
SELECT *
FROM MyTable
WHERE NOT myCol <=> 3
Unfortunately SQL Server doesn't yet support either of these syntaxes.
不幸的是，SQL Server还不支持这些语法。
#2
3
You must handle the NULLs one way or another, since expressions involving NULL evaluate to Unknown. If you want, you could instead do:
您必须以这种或那种方式处理NULL，因为涉及NULL的表达式计算为Unknown。如果你愿意，你可以改为：
select *
from MyTable
where isnull(MyColumn, -1) <> 3
But this involves a magic number (-1), and is arguably less readable than the original test for IS NULL.
但是这涉及一个幻数（-1），可以说比IS NULL的原始测试可读性差。
Edit: and, as SQLMenace points out, is not SARGable.
编辑：并且，正如SQLMenace指出的那样，不是SARGable。
#3
2
Whenever you test for a value all NULLs are omitted – after all, you are testing whether the value in some column passes certain criteria and NULL is not a value.
每当您测试一个值时，所有NULL都被省略 - 毕竟，您正在测试某些列中的值是否通过某些条件而NULL不是值。
#4
1
Do I always have to include the IS NULL or can I set it up so a where clause myCol <>3 will return rows that have Null as value for my Col?
我是否总是必须包含IS NULL或者我可以设置它以使where子句myCol <> 3将返回具有Null作为我的Col的值的行？
You always, always, always have to include is null.
总是，总是必须包含null。
Because 3 does not equal Not/Applicable and it does not equal Unkown.
因为3不等于Not / Applicable并且它不等于Unkown。
#5
0
because you can't compare NULL to anything else, NULL is not even equal to NULL
因为你无法将NULL与其他任何东西进行比较，所以NULL甚至不等于NULL
DECLARE @i INT
DECLARE @i2 INT
SELECT @i = NULL, @i2 = NULL
IF @i = @i2
PRINT 'equal'
ELSE
PRINT 'not equal'
#1
15
I think your approach is fine:
我认为你的方法很好：
SELECT *
FROM MyTable
WHERE myCol <> 3 OR myCol IS NULL
Since you are asking for alternatives, another way to do it is to make your column NOT NULL and store another (otherwised unused) value in the database instead of NULL - for example -1. Then the expression myCol <> 3 will match your fake-NULL just as it would with any other value.
由于您要求替代方法，另一种方法是使您的列NOT NULL并在数据库中存储另一个（其他未使用的）值而不是NULL - 例如-1。然后表达式myCol <> 3将匹配您的假NULL，就像它与任何其他值一样。
SELECT *
FROM MyTable
WHERE myCol <> 3
However in general I would recommend not to use this approach. The way you are already doing it is the right way.
但总的来说，我建议不要使用这种方法。你正在做的方式是正确的方式。
Also it might be worth mentioning that several other databases support IS DISTINCT FROM which does exactly what you want:
另外值得一提的是，其他几个数据库支持IS DISTINCT FROM，它可以完全满足您的需求：
SELECT *
FROM MyTable
WHERE myCol IS DISTINCT FROM 3
MySQL has the NULL-safe equal which can also be used for this purpose:
MySQL具有NULL安全相等，也可用于此目的：
SELECT *
FROM MyTable
WHERE NOT myCol <=> 3
Unfortunately SQL Server doesn't yet support either of these syntaxes.
不幸的是，SQL Server还不支持这些语法。
#2
3
You must handle the NULLs one way or another, since expressions involving NULL evaluate to Unknown. If you want, you could instead do:
您必须以这种或那种方式处理NULL，因为涉及NULL的表达式计算为Unknown。如果你愿意，你可以改为：
select *
from MyTable
where isnull(MyColumn, -1) <> 3
But this involves a magic number (-1), and is arguably less readable than the original test for IS NULL.
但是这涉及一个幻数（-1），可以说比IS NULL的原始测试可读性差。
Edit: and, as SQLMenace points out, is not SARGable.
编辑：并且，正如SQLMenace指出的那样，不是SARGable。
#3
2
Whenever you test for a value all NULLs are omitted – after all, you are testing whether the value in some column passes certain criteria and NULL is not a value.
每当您测试一个值时，所有NULL都被省略 - 毕竟，您正在测试某些列中的值是否通过某些条件而NULL不是值。
#4
1
Do I always have to include the IS NULL or can I set it up so a where clause myCol <>3 will return rows that have Null as value for my Col?
我是否总是必须包含IS NULL或者我可以设置它以使where子句myCol <> 3将返回具有Null作为我的Col的值的行？
You always, always, always have to include is null.
总是，总是必须包含null。
Because 3 does not equal Not/Applicable and it does not equal Unkown.
因为3不等于Not / Applicable并且它不等于Unkown。
#5
0
because you can't compare NULL to anything else, NULL is not even equal to NULL
因为你无法将NULL与其他任何东西进行比较，所以NULL甚至不等于NULL
DECLARE @i INT
DECLARE @i2 INT
SELECT @i = NULL, @i2 = NULL
IF @i = @i2
PRINT 'equal'
ELSE
PRINT 'not equal'