当结果为NULL时，NOT IN子查询失败 [英] NOT IN subquery fails when there are NULL-valued results

问题描述

对不起，我不知道该如何表达，但是在where子句中有以下内容:

person_id not in (
    SELECT distinct person_id
    FROM protocol_application_log_devl pal
    WHERE pal.set_id = @set_id
)

当子查询不返回任何结果时，我的整个选择都无法返回任何内容.要解决此问题，我在子查询中将person_id替换为isnull(person_id, '00000000-0000-0000-0000-000000000000').

这似乎可行，但是有更好的方法来解决这个问题吗?

解决方案

最好还是使用NOT EXISTS:

WHERE NOT EXISTS(
    SELECT 1 FROM protocol_application_log_devl pal
    WHERE pal.person_id = person_id
     AND  pal.set_id = @set_id
)

我应该使用NOT IN，OUTER APPLY ，左外联接，除还是不存在?

我看到了很多但希望我没有的模式没有出现.什么时候 我畏缩了，我看到了这种模式.但不是出于性能原因-之后 总而言之，在这种情况下，它创建了一个足够体面的计划:

主要问题是，如果目标 列为可空(SQL Server将其作为左半反符号处理 加入，但无法可靠地告诉您右侧的NULL是否相等 到-或不等于-左侧的参考).还， 即使该列为NULLable，优化也会表现出不同的效果，即使 它实际上不包含任何NULL值

对此查询模式使用关联的NOT EXISTS ，而不是NOT IN. 始终.在所有方面，其他方法可能会在性能上与之抗衡 其他变量相同，但是所有其他方法都介绍 性能问题或其他挑战.

Sorry guys, I had no idea how to phrase this one, but I have the following in a where clause:

person_id not in (
    SELECT distinct person_id
    FROM protocol_application_log_devl pal
    WHERE pal.set_id = @set_id
)

When the subquery returns no results, my whole select fails to return anything. To work around this, I replaced person_id in the subquery with isnull(person_id, '00000000-0000-0000-0000-000000000000').

It seems to work, but is there a better way to solve this?

解决方案

It is better to use NOT EXISTS anyway:

WHERE NOT EXISTS(
    SELECT 1 FROM protocol_application_log_devl pal
    WHERE pal.person_id = person_id
     AND  pal.set_id = @set_id
)

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

A pattern I see quite a bit, and wish that I didn't, is NOT IN. When I see this pattern, I cringe. But not for performance reasons – after all, it creates a decent enough plan in this case:

The main problem is that the results can be surprising if the target column is NULLable (SQL Server processes this as a left anti semi join, but can't reliably tell you if a NULL on the right side is equal to – or not equal to – the reference on the left side). Also, optimization can behave differently if the column is NULLable, even if it doesn't actually contain any NULL values

Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. Always. Other methods may rival it in terms of performance, when all other variables are the same, but all of the other methods introduce either performance problems or other challenges.

这篇关于当结果为NULL时，NOT IN子查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！