问题描述
对不起,我不知道该如何表达,但是在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子查询失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!