SQL query with NOT IN and WHERE relation with GUIDs

Question

There are two tables Document and DocumentPos. In Document there is column GUID and in DocumentPos is column DocumentGUID which refers to table Document.

I want to have every row in DocumentPos where its DocumentGUID has now row in Documents' GUID.

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
)

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.

But select * from Document where GUID = 'B479BCB72334424DAC1B7CC26880DAB8' returns 0 rows.

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:

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

Answer 1

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.

I think that you want:

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
)