sql - 如何使用 JOIN 而不是 IN 选择一个人所属的所有组的名称？

Question

考虑以下简化示例:

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
);

这个输出:

1|One2|Two

最后一个 SELECT 的正确 JOIN 是什么？

Answer 1

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

---

注意:这等同于您的 IN 语句，因为您专门针对一个人进行过滤。如果你过滤多人，情况就不同了。例如，假设某个具有 pid=2 的人也在组 1 中并且您执行以下 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)

这会返回组 1 两次(与您的 IN 解决方案相反，后者只会返回每个组一次)。为了解决这个问题，你需要在SELECT之后添加DISTINCT关键字或者添加GROUP BY g.gid, g.name 在 SQL 的最后。如果您将此答案用作将 IN 转换为 JOIN 的一般规则，则应牢记这一点。