SQL JOIN的WHERE条件为负 [英] SQL JOIN with negative WHERE condition

问题描述

我有两个表Document和Label(不是我的真实情况，我使用类推).一个文档可以有N个标签.当我需要选择列出了标签的文档时，我可以轻松地做到这一点

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',...)

然后它不起作用.无论如何，所有具有多于一个标签的文档(其中一个标签在列表中)将被返回.因为结合了Document和那些剩余标签(未列出的标签)的raws只会与where条件匹配，因此查询将返回我不想返回的Documents.

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.

我实际上正在像Java Spring JPA类型的查询中那样进行查询.我需要为我的过滤框架解决这种情况.但我认为最好先在SQL级别解决此问题.

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.

为简单起见，我们可以用！="代替"not in".问题还是一样.

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

推荐答案

您可以使用LEFT JOIN来选择所有不匹配的行:

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

或使用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',...)
)

或使用NOT IN:

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

查看简化的演示.

See a simplified demo.

这篇关于SQL JOIN的WHERE条件为负的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！