具有 NOT IN 和 WHERE 关系的 SQL 查询与 GUID

2022-01-23 00:00:00 sql subquery sql-delete sql-server sql-null

有两个表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
)


	
		相关文章