why this left join query failed to load all the data in left table?

users table

+-----+-----------+
| id  | username  |
+-----+-----------+
|   1 | tom       |
|   2 | jelly     |
|   3 | foo       |
|   4 | bar       |
+-----+-----------+

groups table

+---开发者_如何转开发-+---------+-----------------------------+
| id | user_id | title                       |
+----+---------+-----------------------------+
|  2 |       1 | title 1                     |
|  4 |       1 | title 2                     |
+----+---------+-----------------------------+

the query

SELECT users.username,users.id,count(groups.title) as group_count 
FROM users 
LEFT JOIN groups 
ON users.id = groups.user_id

result

+----------+----+-------------+
| username | id | group_count |
+----------+----+-------------+
| tom      |  1 |           2 |
+----------+----+-------------+

where is the rest users' info? the result is the same as inner join , shouldn't left join return all left table's data?

PS:I'm using mysql

Don't you need a group by clause?

group by users.username,users.id

not knowing much about mysql but in mssql you would need this for it to run

What do you expect the group_count column to be, had all the users been returned? If you expected zero for users without groups, which would seem natural, how would it reach that result and still output at least one row?