具有 NOT IN 和 WHERE 关系的 SQL 查询与 GUID-数据库问题

SQL query with NOT IN and WHERE relation with GUIDs(具有 NOT IN 和 WHERE 关系的 SQL 查询与 GUID)

本文介绍了具有 NOT IN 和 WHERE 关系的 SQL 查询与 GUID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有两个表Document和DocumentPos.在 Document 中有列 GUID 并且在 DocumentPos 中有列 DocumentGUID 引用表 Document.


There are two tables Document and DocumentPos. In Document there is column GUID and in DocumentPos is column DocumentGUID which refers to table Document.
我想在 DocumentPos 中的每一行都有它的 DocumentGUID 现在有Documents' GUID.

  I want to have every row in DocumentPos where its DocumentGUID has now
  row in Documents' GUID.
我有这个返回 0 行的查询:
I've this query which returns 0 rows:
select *
FROM             Document d,
                 DocumentPos dp
WHERE            d.GUID = dp.DocumentGUID
AND              dp.DocumentGUID NOT IN (
                 SELECT d.GUID
                 FROM Document
)

但是当我执行 select * from documentpos 时，它会返回例如一行带有 DocumentGUID= B479BCB72334424DAC1B7CC26880DAB8 的行.而这个DocumentGUID 是NOT IN 表Document 作为GUID.
But when when I execute select * from documentpos it returns for example a row with DocumentGUID= B479BCB72334424DAC1B7CC26880DAB8. And this DocumentGUID is NOT IN table Document as a GUID. 
但 select * from Document where GUID = 'B479BCB72334424DAC1B7CC26880DAB8' 返回 0 行.
我想像这样构建查询，因为它应该成为 DELETE 语句:
I want to build the query like this because it should become a DELETE statement:
DELETE           dp
FROM             Document d,
                 DocumentPos dp
WHERE            d.GUID = cp.DocmentGUID
AND              dp.DocumentGUID NOT IN (
                 SELECT d.GUID
                 FROM Document
)

第二个问题我也想知道:
Second question what I'm also wondering:
为什么括号里的不是FROM d，只能是FROM Document?

  Why is in the brackets not FROM d possible and only FROM Document?

推荐答案
NOT IN 对于 NULL 来说很棘手.您可以使用 NOT EXISTS 代替，它是 null 安全的.另外，我看不出你为什么需要在外部查询中引入 document 表.
NOT IN is tricky with NULLs. You can use NOT EXISTS instead, which is null-safe. Also, I cannot see why you need to bring in the document table in the outer query.
我认为你想要:
select *
from documentpos dp
where not exists (
    select 1 from document d where d.guid = dp.documentguid
)

您可以将其转换为删除语句，如下所示:
You can turn this to a delete statement as follows:
delete dp
from documentpos dp
where not exists (
    select 1 from document d where d.guid = dp.documentguid
)


                        这篇关于具有 NOT IN 和 WHERE 关系的 SQL 查询与 GUID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！