SQL"IS"如何执行?和"="运营商有何不同? [英] How do the SQL "IS" and "=" operators differ?

问题描述

我正在构建一些使用参数化值的准备好的语句.例如:

I am building some prepared statements that use parametrized values. As an example:

SELECT * FROM "Foo" WHERE "Bar"=@param

有时@param可能是NULL.在这种情况下，我希望查询返回Bar为NULL的记录，但是上面的查询不会这样做.我了解到可以使用IS运算符.换句话说:

Sometimes @param might be NULL. In such cases, I want the query to return records where Bar is NULL, but the above query will not do that. I have learned that I can use the IS operator for this. In other words:

SELECT * FROM "Foo" WHERE "Bar" IS @param

除了对NULL的不同处理之外，还有其他两种方式可以使上述两个语句的行为有所不同?如果@param不是NULL，而是5，该怎么办?在这种情况下，使用IS运算符是否安全(明智)?我应该采取其他方法吗?

Aside from the differing treatment of NULL, are there any other ways in which the above two statements will behave differently? What if @param is not NULL, but is instead, let's say, 5? Is using the IS operator in that case a safe (and sane) thing to do? Is there some other approach I should be taking?

推荐答案

您想从Foo中获取Bar = @param的记录，或者如果@param为null，Bar为null的记录.某些建议的解决方案将使用nonnull @param为您提供空记录，这听起来不像您的要求.

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.

Select * from Foo where (@param is null and Bar is null) or (Bar = @param)

这不是说这是Oracle还是SQL Server还是另一个RDBMS，因为它们各自实现的助手功能略有不同. SQL的ISNULL(第一，第二)类似于NVL(第一，第二).我喜欢SQL Server的COALESCE()的一般适用性.

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.

IS比较仅用于空比较.

The IS comparison is only for null comparisons.

如果您使用的是SQL Server，并且确实需要一个不同的3VL逻辑真值表来解决您的问题(也就是说，如果您特别需要将"NULL = NULL"设置为"true"，并且也认识到已弃用该命令并限制了您的原因(通常不是一个好主意)，在您的代码块中，您可以使用指令

If you are using SQL Server and if you really need a 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

将ANSI_NULLS设置为关闭

SET ANSI_NULLS OFF

下面是BOL: http://msdn.microsoft.com/en-us/library/ms188048.aspx

这篇关于SQL"IS"如何执行?和"="运营商有何不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！