Implementando uma consulta, dois tipos de estratégias

As tabelas ainda não foram criadas, mas para simplificar existem as tabelas a groupse an . itemsUm grupo contém um id de item (e um id de grupo). E vários grupos podem conter o mesmo id de item.

Por exemplo

CREATE TABLE groups (
          gid int,
          iid int,
          primary key(gid,iid)
);
CREATE TABLE items (
          iid int primary key,
          label char(5)
);

e vamos colocar alguns dados neles, para ajudar a visualizar o próximo problema

insert into items values (1,"pen"),(2,"gum"),(3,"cat"),(4,"dog"),
                         (5,"hug"),(6,"art"),(7,"fun");

insert into groups values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,1),
                         (3,4),(4,2),(4,3),(4,5),(4,6),(4,7);

Recuperar itens de vários grupos (por exemplo, grupos 1,2,3,4) usa a DISTINCTpara remover duplicatas

(1) SELECT DISTINCT i.iid,i.label FROM groups g JOIN items i ON g.iid=i.iid 
    WHERE g.gid in (1,2,3,4) ORDER BY i.iid;

Mas como as tabelas eventualmente conterão mais dados, essa consulta usando uma subconsulta seria mais eficiente

(2) SELECT i.iid,i.label FROM items i 
    JOIN (SELECT DISTINCT iid FROM groups WHERE gid IN (1,2,3,4)) AS s
    ON i.iid=s.iid ORDER BY i.iid;

já que distinctse aplica apenas aos ids dos itens, apesar da sobrecarga de usar uma subconsulta?

Ou este sugerido por @hypercube (ver comentários):

(3) SELECT i.iid,i.label FROM items AS i 
    WHERE EXISTS
       (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid)
    ORDER BY i.iid

Para informação, eventualmente

• a tabela de itens terá entre 50 a 100 mil linhas
• um grupo provavelmente terá de 5 a 10 mil itens
• os IDs de grupos a serem selecionados em uma única consulta estão no intervalo de 5 a 20
• probabilidade de um item fazer parte de 2 grupos selecionados: 50%
• probabilidade de um item fazer parte de 3 grupos selecionados: 30%
• probabilidade de um item fazer parte de 4 grupos selecionados: 10%