sql-server - 当有 NULL 值的结果时 NOT IN 子查询失败

Question

对不起，我不知道如何表达这个，但我在 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') 。

它似乎有效，但有没有更好的方法来解决这个问题？

Answer 1

无论如何最好使用 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
)

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.