与GUID具有NOT IN和WHERE关系的SQL查询 [英] SQL query with NOT IN and WHERE relation with GUIDs

问题描述

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

推荐答案

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
)

您可以将其转换为delete语句，如下所示:

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
)

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