选择被分组的行，即使它们不在另一个表中

Question

给SQL问题起标题很难！如果您能想出更好的描述方法，请随意更改！

我的设置非常典型：3个表，users，groups和group_members。

这是一个 SQL Fiddle: http://sqlfiddle.com/#!2/23712/1

我想知道哪些组属于哪些用户。

因此，我正在运行：

SELECT u.id, u.firstname, u.lastname,
GROUP_CONCAT(m.group_id) as groups
FROM group_members m, users u
WHERE m.user_id = u.id
GROUP BY id
ORDER BY u.lastname ASC

这很好，向我显示了用户的姓名和他们所在的组。

我的问题是，没有任何组的用户不会出现，因为WHERE子句当然不匹配它们。

如何返回没有任何组的用户？（在上面的SQL Fiddle中，我想要另一行Zack Jones，显示他是在组0、NULL或类似的东西中！）

- Rich Bradshaw

Answer 1

如果你不确定匹配是否可用，应该在其中一个表上使用LEFT JOIN。在你的情况下，你应该在group_members表上使用LEFT JOIN。

SELECT u.id,
       u.firstname,
       u.lastname,
       GROUP_CONCAT(m.group_id) AS groups
FROM   users
       LEFT JOIN group_members
              ON users.id = group_members.user_id
GROUP  BY id
ORDER  BY users.lastname ASC

- Ben Fransen

---

我觉得有趣的是，人们总是试图“编辑”帖子，因为代码的样式似乎不适合他们的世界，而不是看到实际答案。 - Ben Fransen

Answer 2

SELECT
    u.id, u.firstname, u.lastname, GROUP_CONCAT(m.group_id) as groups
FROM
    users u
    LEFT JOIN
    group_members m ON  m.user_id = u.id
GROUP BY u.id, u.firstname, u.lastname
ORDER BY u.lastname ASC

一般情况下，使用明确的JOIN，而不是隐式WHERE JOIN。在这种情况下，使用LEFT OUTER JOIN。

此外，不要依赖于MySQL的GROUP BY扩展：它们不总是按预期工作。

- gbn

Answer 3

您需要在group_members上使用users表的LEFT JOIN:

SELECT u.id, 
  u.firstname, 
  u.lastname, 
  GROUP_CONCAT(m.group_id) as groups 
FROM users u
LEFT JOIN group_members m 
  ON u.id = m.user_id
GROUP BY u.id, u.firstname, 
  u.lastname
ORDER BY u.lastname ASC

请参考附带演示的SQL Fiddle

或者您可以使用RIGHT JOIN将group_members与users连接起来

SELECT u.id, 
  u.firstname, 
  u.lastname, 
  GROUP_CONCAT(m.group_id) as groups 
FROM group_members m
RIGHT JOIN users u
  ON m.user_id = u.id 
GROUP BY id, u.firstname, 
  u.lastname
ORDER BY u.lastname ASC

see SQL Fiddle with Demo

- Taryn

---

非常感谢！LEFT或RIGHT连接有什么好处吗？ - Rich Bradshaw

@RichBradshaw 除了表在语句中的位置不同之外，它们是相同的...users表只是在RIGHT和LEFT版本之间切换了位置。我建议您查看一个很好的关于连接的可视化解释。 - Taryn

Answer 4

SELECT u.id, u.firstname, u.lastname,
GROUP_CONCAT(m.group_id) as groups
FROM users u
LEFT JOIN group_members m on m.user_id = u.id
GROUP BY u.id
ORDER BY u.lastname ASC

- Brian Hoover

Answer 5

在这些情况下，外连接非常有用。我已经交换了用户和组成员m的位置，以执行左外连接。您还应该避免在where子句中进行JOIN操作。

SELECT u.id, u.firstname, u.lastname, GROUP_CONCAT(m.group_id) as groups 
FROM users u LEFT OUTER JOIN group_members m 
ON m.user_id = u.id 
GROUP BY id 
ORDER BY u.lastname ASC

- StuartLC

Answer 6

SELECT 
        u.id, 
        u.firstname, 
        u.lastname,
        m.groups
FROM users u
LEFT JOIN (
            SELECT  user_id,
                    GROUP_CONCAT(m.group_id) as groups  
            FROM group_members
           ) as m on m.user_id = u.id
WHERE m.user_id = u.id
GROUP BY id
ORDER BY u.lastname ASC

在您的查询中，您正在使用笛卡尔积。避免使用此方法，因为它会带来太多不必要的结果，而应该使用连接，因为它们更快速。

- Muhammad Raheel

---

这样做是可行的，但不必使用派生表（内联视图）来获取指定的结果集。使用派生表可能会影响性能...我们在需要时使用它们，在不需要时避免使用它们。OP查询不会产生笛卡尔积；OP查询执行JOIN，连接谓词在WHERE子句中。您完全正确地建议使用JOIN关键字（代替逗号运算符），并将连接谓词包含在ON子句中（而不是在WHERE子句中）。我们还喜欢将所有非聚合项包含在GROUP BY子句中。 - spencer7593