SQL“IS”和“=”运算符有何不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5423751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do the SQL "IS" and "=" operators differ?
提问by Dan Moulding
I am building some prepared statements that use parametrized values. As an example:
我正在构建一些使用参数化值的准备好的语句。举个例子：
SELECT * FROM "Foo" WHERE "Bar"=@param
Sometimes @parammight be NULL. In such cases, I want the query to return records where Baris NULL, but the above query will not do that. I have learned that I can use the ISoperator for this. In other words:
有时@param可能是NULL。在这种情况下，我希望查询返回记录 where Baris NULL，但上面的查询不会这样做。我了解到我可以IS为此使用运算符。换句话说：
SELECT * FROM "Foo" WHERE "Bar" IS @param
Aside from the differing treatment of NULL, are there any other ways in which the above two statements will behave differently? What if @paramis not NULL, but is instead, let's say, 5? Is using the ISoperator in that case a safe (and sane) thing to do? Is there some other approach I should be taking?
除了对 的不同处理之外NULL，是否还有其他方式使上述两个语句的行为有所不同？如果@param不是NULL，而是，比方说，5呢？IS在这种情况下使用运算符是安全（和理智）的事情吗？我应该采取其他方法吗？
采纳答案by user662852
You want records from Foo where Bar = @param, or if @param is null, where Bar is null. Some of the proposed solutions will give you null records with nonnull @param, which does not sound like your requirement.
您需要来自 Foo 的记录，其中 Bar = @param，或者如果 @param 为 null，则 Bar 为 null。一些建议的解决方案将为您提供非空@param 的空记录，这听起来不像您的要求。
Select * from Foo where (@param is null and Bar is null) or (Bar = @param)
This doesn't say whether this is Oracle or SQL Server or another RDBMS, because they each implement slightly different helper functions. SQL's ISNULL(first, second) like NVL(first, second). I like SQL Server's COALESCE() for the general applicability.
这并没有说明这是 Oracle 还是 SQL Server 或其他 RDBMS，因为它们各自实现的辅助函数略有不同。SQL 的 ISNULL(first, second) 就像 NVL(first, second)。我喜欢 SQL Server 的 COALESCE() 的普遍适用性。
The IS comparison is only for null comparisons.
IS 比较仅用于空比较。
If you are using SQL Server and if you really needa different 3VL logic truth table to solve your problem (that is, if you have a specific need for "NULL=NULL" to be "true" at some point in time, and also recognize that this is deprecated and barring your reasons, not a good idea in general), within your code block you can use the directive
如果您正在使用 SQL Server 并且您确实需要不同的 3VL 逻辑真值表来解决您的问题（也就是说，如果您有特定的需要“NULL=NULL”在某个时间点为“真”，并且认识到这是不推荐使用的，除非你有理由，一般来说这不是一个好主意），在你的代码块中，你可以使用指令
SET ANSI_NULLS OFF
设置 ANSI_NULLS 关闭
Here's the BOL on it: http://msdn.microsoft.com/en-us/library/ms188048.aspx
这是它的 BOL：http: //msdn.microsoft.com/en-us/library/ms188048.aspx
回答by Dan J
You may be thinking about this incorrectly. If you're talking about SQL Server, for example (since that's what I have to hand), your second example will result in a syntax error. The value on the right-hand side of IS cannot be 5.
您可能会错误地考虑这一点。例如，如果您在谈论 SQL Server（因为这是我必须处理的），那么您的第二个示例将导致语法错误。IS 右侧的值不能为 5。
To explain, consider MSDN's explanation of these two operators in T-SQL (note that asking about "SQL" and about "SQL Server" are not necessarily the same).
要解释，请考虑 MSDN 对 T-SQL 中这两个运算符的解释（注意询问“SQL”和询问“SQL Server”不一定相同）。
Notice something important, there. There is no such thing as the "IS" operatorin T-SQL. There is specifically the <expression> IS [NOT] NULLoperator, which compares a single expression to NULL.
注意一些重要的事情，在那里。还有就是为“IS”运营商没有这样的事情在T-SQL。有专门的<expression> IS [NOT] NULL运算符，它将单个表达式与 NULL 进行比较。
That's not the same thingas the =operator, which compares twoexpressions to each other, and has certain behavior when one or both of the expressions happens to be NULL!
这与=运算符不同，运算符将两个表达式相互比较，并在其中一个或两个表达式碰巧为 NULL 时具有某些行为！
回答by Hari Menon
Edit: (Update from OP: This doesn't do what I If @param is 5, then I want to see only records where Bar is 5. I want to see records where Bar is NULL if, and only if, @param is NULL. I apologize if my question didn't make that clear.)
编辑：（ 从 OP 更新：如果@param 是 5，那么我只想查看 Bar 为 5 的记录。当且仅当 @param 为时，我想查看 Bar 为 NULL 的记录NULL。如果我的问题没有说清楚，我深表歉意。）
In that case, I think you should try something like this:
在这种情况下，我认为你应该尝试这样的事情：
SELECT * FROM Foo WHERE Bar=@param OR (Bar IS NULL AND @param IS NULL)
Previous post:
上一篇：
Why not simply use OR ?
为什么不简单地使用 OR ？
SELECT * FROM "Foo" WHERE "Bar"=@param OR "Bar" IS NULL
In SQL Server, you can use ISNULL:
在 SQL Server 中，您可以使用 ISNULL：
SELECT * FROM "Foo" WHERE ISNULL("Bar",@param)=@param
回答by HLGEM
I don't know what version of SQL you are using but IS makes no sense in the context you just described. I get a syntax error if I try to use it the way you described. Why would you want to use it over = anyway? This is the common usage and the one software maintainers woudl expect to find.
我不知道您使用的是哪个版本的 SQL，但 IS 在您刚刚描述的上下文中没有任何意义。如果我尝试按照您描述的方式使用它，则会出现语法错误。你为什么要在 = 上使用它？这是常见的用法，也是软件维护人员希望找到的一种用法。
回答by justacoder
What specific database are you using?
你使用的是什么特定的数据库？
If you're doing searches based on null (or not null), using IS is the way to go. I cannot provide a technical reason but I use this syntax all the time.
如果您基于空值（或非空值）进行搜索，则使用 IS 是一种可行的方法。我无法提供技术原因，但我一直使用这种语法。
SELECT * FROM Table WHERE Field IS NULL
SELECT * FROM Table WHERE Field IS NOT NULL
