在 TSQL 中使用 ISNULL 的 Sargable 查询 [英] Sargable queries using ISNULL in TSQL

问题描述

我希望在我的查询中防止非 sargable 表达式，这是检查空条件的更好方法吗?

I'm looking to prevent non-sargable expressions in my queries, which is the better way to check for a null condition?

AND c.Account IS NOT NULL 
AND c.Account <> ''

或

AND ISNULL(c.Account,'') <> ''

<小时>

我突然意识到Account 来自LEFT JOIN，因此它可能为空.我想要它们只相交的情况，这意味着我真的应该只使用 INNER JOIN 吧?谢谢你的脸;)

---

It dawned on me to point out that Account is coming from a LEFT JOIN so it may be null. I want the cases where they only intersect, which means I should really just use an INNER JOIN huh? Thanks for the facepalms ;)

然而，忽略那令人作呕的自我实现，我仍然想知道在我不能将 Account 设为 NOT NULL 列的一般情况下的答案.

However, overlooking that nauseating self realization, I still want to know the answer to this in the general case where I can't make Account a NOT NULL column.

推荐答案

C.Account <>'' 等价于 ISNULL( c.Account, '' ) <>''

SQL Server 可能足够聪明，可以将 IsNull 转换为等效的 SARG 表达式，但是如果您一心想要使用函数，那么 Coalesce 是更好的选择，因为它是 SQL 标准的一部分，允许使用多个值(而不是仅仅两个与 IsNull)并避免使用很可能是 Microsoft 在 IsNull 中设计的最容易混淆的函数名称.

SQL Server is probably smart enough to translate IsNull into the equivalent SARG expression but if you are bent on using a function, then Coalesce is a better choice because it is part of the SQL Standard, allows for multiple values (instead of just two with IsNull) and avoids using quite possibly the most confusing function name Microsoft ever devised in IsNull.

这篇关于在 TSQL 中使用 ISNULL 的 Sargable 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！