SQL JOIN с отрицательным условием WHERE

У меня есть две таблицы Document и Label (не мой реальный случай, я использую аналогию). Один документ может иметь N меток. Когда мне нужно выбрать документы с перечисленными метками, я могу легко сделать это

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

Как написать запрос, где мне нужны документы, которые НЕ имеют перечисленных меток? Когда я делаю это

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

тогда это не работает. Все документы, имеющие более одной метки, где одна из этих меток находится в списке, будут возвращены в любом случае. Потому что необработанные данные с комбинацией документа и этих оставшихся меток (не перечисленных меток) будут просто соответствовать условию where, поэтому запрос будет возвращать документы, которые я не хочу возвращать.

На самом деле я работаю над таким запросом в типизированных запросах Java Spring JPA. Мне нужно решить этот случай для моей системы фильтрации. Но я думаю, что лучше сначала решить эту проблему на уровне SQL.

Кстати, мы можем заменить «не в» на «!=» для простоты. Проблема все та же.

Любая идея для простого решения? заранее спасибо


sql join sql-in
person Lukas S    schedule 23.03.2020    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вы можете сделать это с помощью LEFT JOIN, где вы выбираете все несопоставленные строки:

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

См. упрощенную демонстрацию.

person forpas    schedule 23.03.2020
comment
Я думаю, что только вариант NOT IN правильный. Первые 2 могут вернуть нежелательный идентификатор. Один идентификатор может иметь метку1, метку2, а также метку3. Из-за label3 будет возвращен идентификатор, но на самом деле его возвращать не следует. - person IMujagic; 24.03.2020
comment
Если document_id в таблице label имеет любое из значений ("label1", "label2",...), то exists вернет true, а not exists вернет false, и этот id не будет в результатах. То же самое относится и к ЛЕВОМУ СОЕДИНЕНИЮ. - person forpas; 24.03.2020
comment
@IMujagic проверьте демо-ссылку в моем ответе. - person forpas; 24.03.2020
comment
правда, я перекомпилировал его сейчас снова .. мой плохой, я неправильно понял первые два, на меня слишком повлияло решение NOT IN :) - person IMujagic; 24.03.2020
comment
Большое спасибо. Мне действительно нужно было обновить этот материал SQL. Это вдруг выглядит очень ясно. - person Lukas S; 24.03.2020
comment
Сделанный. Последняя проблема, с которой я столкнулся, заключается в том, что я не знаю, как это сделать в JPA Criteria API. Но это другая тема - person Lukas S; 24.03.2020

arrow_upward
0
arrow_downward

Если вы ищете документы без меток, вам нужно внешнее соединение

select D.id 
from document D
left join label L on  D.id = L.document_id
where L.value is null
person Fran Cerezo    schedule 23.03.2020

arrow_upward
0
arrow_downward

Если вы хотите получать документы, у которых нет ни одной из заданных меток, вы можете исключить идентификаторы, которые вы получили бы с положительным запросом, который у вас уже есть, используя not in. Если вы хотите исключить некоторые метки при поиске других, вы можете объединить оба, используя:

    where id not in (
      select D.id from document D
      join label L on  D.id = L.document_id
      where L.value in('label1', ...)
    ) and id in (
      select D.id from document D
      join label L on  D.id = L.document_id
      where L.value in('label2', ...)
    )
person T. Pieper    schedule 23.03.2020