SQL Server 返回不相等的行 <>到一个值和 NULL [英] SQL Server return Rows that are not equal <> to a value and NULL
问题描述
我有一个表，其中有一列值可以是 rowTypeID = (1,2,3, or null) .我想编写一个查询，该查询返回其中没有值为 3 的任何行.在这个例子中，我想要所有 NULL 行以及所有 1,2 行我只是不想要值为 3 的行
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
设置 ANSI null 当前为数据库设置了 ON.
Set ANSI null ON is currently set for the database.
我很好奇为什么我不能写
I'm curious as to why I can't write
select * from myTable where myCol <> 3
此查询不会返回 myCol 列中任何为 NULL 的行
This query will not return any rows that have NULL in the myCol column
我必须写
select * from my Table where myCol <> 3 or myCol Is NULL
我是否总是必须包含 IS NULL 或者我可以设置它以便 where 子句 myCol <>3 将返回具有 Null 作为我的 Col 值的行
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
推荐答案
我认为你的方法很好:
SELECT *
FROM MyTable
WHERE myCol <> 3 OR myCol IS NULL
既然您要求替代，另一种方法是使您的列 NOT NULL 并在数据库中存储另一个(否则未使用的)值而不是 NULL- 例如 -1.然后表达式 myCol <>3 将匹配您的 fake-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.
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.
另外值得一提的是，其他几个数据库都支持 IS DISTINCT FROM，它完全符合您的要求:
Also it might be worth mentioning that several other databases support IS DISTINCT FROM which does exactly what you want:
SELECT *
FROM MyTable
WHERE myCol IS DISTINCT FROM 3
MySQL 具有 NULL-safe equal 也可以用于此目的:
MySQL has the NULL-safe equal which can also be used for this purpose:
SELECT *
FROM MyTable
WHERE NOT myCol <=> 3
遗憾的是，SQL Server 尚不支持这两种语法.
Unfortunately SQL Server doesn't yet support either of these syntaxes.
这篇关于SQL Server 返回不相等的行 <>到一个值和 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
