Selecting rows that are grouped, even if they aren't in that other table

Question

Titling SQL questions is hard! Feel free to change it if you can think how to describe this better!

I have a pretty typical set up: 3 tables, users, groups, and group_members.

Here's an SQL Fiddle: http://sqlfiddle.com/#!2/23712/1

What I want to know is which groups are which users in.

So, I'm running:

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

Which is cool, and shows me the users name and what groups they are in.

My problem is that users who aren't in any groups don't show up, as of course the WHERE bit doesn't match them.

How can I also return the users who aren't in any group? (In the SQL Fiddle above, I want another row for Zack Jones, showing that he is either in group 0, or NULL or something!)

score 4 · Answer 1 · edited Aug 29 '12 at 15:05

4

You should use a LEFT JOIN on the table where you are not sure if matches will available. In your case you should LEFT JOIN the group_members table.

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

edited Aug 29 '12 at 15:05

Adi

5,089
6
33
47

answered Aug 29 '12 at 14:46

Ben Fransen

10,884
18
76
129

I kinda find it funny how people always try to 'edit' a post because the styling of the code doesn't seem to fit in their world rather than to see the actual answer. – Ben Fransen Aug 31 '12 at 09:05

score 4 · Answer 2 · edited May 23 '17 at 12:20

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, u.firstname, u.lastname
ORDER BY u.lastname ASC

Use explicit JOINs, not implied-WHERE JOINS generally. In this case, use LEFT OUTER JOIN

Also, don't rely on the MySQL GROUP BY extensions: they don't always work as expected

edited May 23 '17 at 12:20

Community

1
1

answered Aug 29 '12 at 14:46

gbn

422,506
82
585
676

Taryn · Accepted Answer · 2012-08-29T14:54:21.720

4

You will want to use a LEFT JOIN of your users table on the group_members:

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

see SQL Fiddle with Demo

Or you can RIGHT JOIN group_members to 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

edited Aug 29 '12 at 14:54

answered Aug 29 '12 at 14:49

Taryn

242,637
56
362
405

Thanks a lot! Is there any benefit for either the LEFT or RIGHT join? – Rich Bradshaw Aug 29 '12 at 15:11
@RichBradshaw they are the same, except for the positioning of where the table is in the statement...the `users` table just switched position between the `RIGHT` and `LEFT` version. There is a great [visual explanation of joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) that I would recommend for review. – Taryn Aug 29 '12 at 15:13

score 1 · Answer 4 · answered Aug 29 '12 at 14:47

1

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

answered Aug 29 '12 at 14:47

Brian Hoover

7,861
2
28
41

score 1 · Answer 5 · answered Aug 29 '12 at 14:47

Outer Joins are useful in these cases. I've swapped users and group_members m around in order to do a left outer join. You should also look at avoiding doing JOINs in a where clause.

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

score 1 · Answer 6 · answered Aug 29 '12 at 14:51

1

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

In your query you are using a cartisian product. Avoid using your method as it will bring too many undesired results instead use joins much faster as they are

answered Aug 29 '12 at 14:51

Muhammad Raheel

19,823
7
67
103

This will work, but it's not necessary to use a derived table (an inline view) to get the specified resultset. The use of a derived table can impact performance... we use them where we need them and avoid them where we don't. The OP query does not produce a Cartesian product; the OP query does perform a JOIN, the join predicate is in the WHERE clause. You are quite right to recommend the use of the JOIN keyword (in place of the comma operator) and to include the join predicates in the ON clause (rather than in the WHERE clause). We also prefer to include all non-aggregates in the GROUP BY cl – spencer7593 Aug 29 '12 at 15:01

Selecting rows that are grouped, even if they aren't in that other table

6 Answers6

see SQL Fiddle with Demo

see SQL Fiddle with Demo