SQL Server 返回不相等的行 <>到一个值和 NULL

本文介绍了SQL Server 返回不相等的行 &lt;&gt;到一个值和 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 MyTableWHERE 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 MyTableWHERE 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 MyTableWHERE myCol IS DISTINCT FROM 3MySQL 具有 NULL-safe equal 也可以用于此目的:MySQL has the NULL-safe equal which can also be used for this purpose:SELECT *FROM MyTableWHERE NOT myCol <=> 3遗憾的是，SQL Server 尚不支持这两种语法.Unfortunately SQL Server doesn't yet support either of these syntaxes. 这篇关于SQL Server 返回不相等的行 &lt;&gt;到一个值和 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！