Блог

У меня есть 3 таблицы:

 Table        columns
---------------------
items        id, name
variations   id, item_id
images       id, variation_id, type

  

image имеет a variation и variation имеет item

Я хочу найти items те, которые не имеют images типа 4 или 5 (типы представляют собой любое целое число 0 ..5 и не могут быть нулевыми)

Приведенный ниже запрос работает, однако он использует подзапрос ОБЪЕДИНЕНИЯ с ограничением NOT IN, которое я считаю неэффективным — поэтому вопрос в том, какой более эффективный способ написать этот запрос.

 SELECT DISTINCT i.id, i.name 
FROM items i 
INNER JOIN variations v 
  ON v.item_id = i.id 
INNER JOIN images vi 
  ON vi.variation_id = v.id 
WHERE i.id NOT IN (SELECT i.id FROM items i 
                  INNER JOIN variations v 
                    ON v.item_id = i.id 
                  INNER JOIN images vi 
                    ON vi.variation_id = v.id 
                  WHERE vi.type = 4
                  UNION
                  SELECT i.id FROM items i 
                  INNER JOIN variations v 
                    ON v.item_id = i.id 
                  INNER JOIN images vi 
                    ON vi.variation_id = v.id 
                  WHERE vi.type = 5)
  

Решение

На основе приведенного ниже ответа Гордона окончательное решение:

 select i.*
from items i
where not exists (select 1
                  from variations v join
                       images im
                       on v.image_id = im.id
                  where v.item_id = i.item_id and i.type in (4, 5)
                 )
and exists (select 1
                  from variations v join
                       images im
                       on v.id = im.variation_id
                       where v.item_id = i.id)
  

Я хочу найти элементы, у которых нет изображений типа 4 или 5

Исходя из вашего описания, это звучит как not exists :

 select i.*
from items i
where not exists (select 1
                  from variations v join
                       images im
                       on v.image_id = im.id
                  where v.item_id = i.item_id and i.type in (4, 5)
                 );
  

Альтернатива ответу Гордона с использованием агрегации:

 SELECT DISTINCT
    i.id,
    i.name 
FROM items i 
INNER JOIN variations v 
    ON v.item_id = i.id 
INNER JOIN images vi 
    ON vi.variation_id = v.id
GROUP BY
    i.id
HAVING
    COUNT(*) FILTER (WHERE vi.type IN (4, 5)) = 0;
  

Обратите внимание, что вышеизложенное предполагает, что id это столбец первичного ключа в items таблице. Если нет, то, возможно, нам следует использовать GROUP BY i.id, i.name вместо этого.