如何使用 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|One
2|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< /code> 在 SQL 的最后。如果您使用此答案作为将 IN 转换为 JOIN 的一般规则，您应该记住这一点。

Answer 2

这是等价的：

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

Answer 3

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

Answer 4

怎么样：

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

Answer 5

之前给出的答案是正确的，但我想指出，您的查询将从在 (pid, gid) （按此顺序）上创建 PRIMARY KEY 中受益。

此查询：

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

然后能够使 people_groups 在 JOIN 中处于领先地位，这很可能会使速度更快，因为 pid 似乎是在链接表中非常有选择性。

或者，您可以创建辅助索引：

CREATE UNIQUE INDEX ux_peoplegroup_p_g ON people_group (pid, gid)

，或者仅

CREATE INDEX ix_peoplegroup_p ON people_group (pid)

当 people_groups 为 InnoDB 时创建。

Answer 6

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