本文介绍了当结果为NULL时,NOT IN子查询失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

对不起,我不知道该如何表达,但是在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 ,左外联接,除还是不存在?

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?

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

08-20 11:22
查看更多