How can I SELECT the names of all groups a person belongs to using a JOIN instead of IN?

Question

Consider the following simplified example:

CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name VARCHAR(100) );

CREATE TABLE people ( pid INTEGER PRIMARY KEY );

CREATE TABLE people_groups (
    gid INTEGER NOT NULL
        CONSTRAINT fk_people_groups_group
        REFERENCES groups(gid),
    pid INTEGER NOT NULL
        CONSTRAINT fk_people_groups_person
        REFERENCES people(pid),
    CONSTRAINT pk_people_groups PRIMARY KEY (gid, pid)
);

INSERT INTO people (pid) VALUES (1);
INSERT INTO groups (gid, name) VALUES (1, 'One');
INSERT INTO groups (gid, name) VALUES (2, 'Two');
INSERT INTO people_groups (gid, pid) VALUES (1,1);
INSERT INTO people_groups (gid, pid) VALUES (2,1);

SELECT gid, name FROM groups WHERE gid IN (
    SELECT gid FROM people_groups WHERE pid = 1
);

This outputs:

1|One
2|Two

What is the correct JOIN for that last SELECT?

Answer 1

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid = 1

---

Note: This is equivalent to your IN statement, because you specifically filter for only one person. If you filter for multiple persons, things are different. For example, assume that some person with pid=2 is also in group 1 and you do the following SELECT:

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid IN (1, 2)

This would return group 1 twice (in contrast to your IN solution, which would return each group only once). To solve this, you need to add the DISTINCT keyword after the SELECT or add GROUP BY g.gid, g.name at the very end of the SQL. You should keep that in mind if you use this answer as a general rule to convert IN to JOIN.

Answer 2

How about:

SELECT g.gid, g.name
  FROM groups AS g JOIN people_groups AS p ON g.gid = p.gid
 WHERE p.pid = 1;

Answer 3

This is the equivalent:

SELECT g.gid, g.name 
FROM groups g 
  INNER JOIN people_groups pg
  ON g.gid = pg.gid
WHERE pg.pid = 1

Answer 4

SELECT g.gid, g.name FROM groups g, people_groups pg 
WHERE g.gid = pg.gid and pg.pid = 1

Answer 5

SELECT gid, name FROM groups 
NATURAL JOIN people_groups 
WHERE pid = 1;

Answer 6

The answers given before are correct, but I'd like to note that you query will benefit from making the PRIMARY KEY on (pid, gid) (in this order).

This query:

SELECT  g.gid
FROM    people_groups pg
JOIN    groups g
ON      g.gid = pg.gid
WHERE   pd.pid = 1

will then be able to make people_groups be leading in the JOIN which most probably will make it much faster, since pid seems to be very selective in the link table.

Alternatively, you can create a secondary index:

CREATE UNIQUE INDEX ux_peoplegroup_p_g ON people_group (pid, gid)

, or just

CREATE INDEX ix_peoplegroup_p ON people_group (pid)

if people_groups is InnoDB.