SQL JOIN with negative WHERE condition

I have two tables Document and Label (not my real case, I am using analogy). One Document can have N Labels. When I need to select Documents that has listed labels I can do easily this

select D.id from document D
join label L on  D.id = L.document_id
where L.value in('label1','label2',...)

How to write a query where I need Documents that DO NOT have listed labels? When I do this

select D.id from document D
join label L on  D.id = L.document_id
where L.value not in('label1','label2',...)

then it does not work. All documents having more then one label where one of those labels is in the list will be returned anyway. Becuause raws with combination of Document and those remaining labels (not listed labels) will simply match the where condition so the query will return Documents I don't want to be returned.

I am actually working on query like that in Java Spring JPA typed queries. I need to solve this case for my filtering framework. But I think it is better to resolve this problem on SQL level first.

By the way we can replace the "not in" with "!=" for simplicity. The problem is still the same.

Any idea for a simple solution? Thank you in advance

Solution

You can do it with a LEFT JOIN where you select all the unmatched rows:

select D.id 
from document D left join label L 
on D.id = L.document_id and L.value in('label1','label2',...)
where L.document_id is null

or with NOT EXISTS:

select D.id from document D 
where not exists (
  select 1 from label L
  where L.document_id = D.id and L.value in('label1','label2',...)
)

or with NOT IN:

select id from document
where id not in (
  select document_id from label 
  where value in('label1','label2',...)
)

See a simplified demo.