MySQL joint table query basic operation left-join common pitfalls

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `role_name` VARCHAR(50) DEFAULT NULL COMMENT 'Role name',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='role table';


insert into `role` VALUES(1, 'Administrator');
insert into `role` VALUES(2, 'General Manager');
insert into `role` VALUES(3, 'Section Chief');
insert into `role` VALUES(4, 'Team Leader');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `role_id` int(11) NOT NULL COMMENT 'Role id',
 `user_name` VARCHAR(50) DEFAULT NULL COMMENT 'User name',
 `sex` int(1) DEFAULT 0 COMMENT 'Gender',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User table';

insert into `user` VALUES(1, 1, 'admin', 1);
insert into `user` VALUES(2, 2, 'Manager Wang', 1);
insert into `user` VALUES(3, 2, 'Manager Li', 2);
insert into `user` VALUES(4, 2, 'Manager Zhang', 2);
insert into `user` VALUES(5, 3, 'Section Chief Wang', 1);
insert into `user` VALUES(6, 3, 'Section Chief Li', 1);
insert into `user` VALUES(7, 3, 'Lv Section Chief', 2);
insert into `user` VALUES(8, 3, 'Section Chief Xing', 1);
insert into `user` VALUES(9, 4, 'Team Leader Fan', 2);
insert into `user` VALUES(10, 4, 'Team Leader Zhao', 2);
insert into `user` VALUES(11, 4, 'Ji Team Leader', 1);

mysql> select * from role;
+----+-----------+
| id | role_name |
+----+-----------+
| 1 | Admin |
| 2 | General Manager|
| 3 | Section Chief |
| 4 | Team Leader|
+----+-----------+
4 rows in set (0.00 sec)

mysql> select * from user;
+----+---------+-----------+------+
| id | role_id | user_name | sex |
+----+---------+-----------+------+
| 1 | 1 | admin | 1 |
| 2 | 2 | Manager Wang | 1 |
| 3 | 2 | Manager Li | 2 |
| 4 | 2 | Manager Zhang | 2 |
| 5 | 3 | Section Chief Wang | 1 |
| 6 | 3 | Section Chief Li | 1 |
| 7 | 3 | Section Chief Lu | 2 |
| 8 | 3 | Section Chief Xing | 1 |
| 9 | 4 | Team Leader Fan | 2 |
| 10 | 4 | Team Leader Zhao | 2 |
| 11 | 4 | Team Leader Ji | 1 |
+----+---------+-----------+------+
11 rows in set (0.00 sec)

mysql> SELECT
  -> id,
  -> user_name AS 'name',
  -> ( CASE WHEN sex = 1 THEN 'Male' WHEN sex = 2 THEN 'Female' ELSE 'Unknown' END ) AS 'Gender'
  -> FROM
  -> USER;
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 1 | admin | Male |
| 2 | Manager Wang | Male |
| 3 | Manager Li | Female |
| 4 | Manager Zhang | Female |
| 5 | Section Chief Wang | Male |
| 6 | Section Chief Li | Male |
| 7 | Section Chief Lu | Female |
| 8 | Section Chief Xing | Male |
| 9 | Team Leader Fan | Female |
| 10 | Team Leader Zhao | Female |
| 11 | Ji Team Leader | Male |
+----+-----------+--------+

mysql> SELECT
  -> r.id,
  -> r.role_name AS role,
  -> count( u.sex ) AS sex
  -> FROM
  -> role r
  -> LEFT JOIN USER u ON r.id = u.role_id
  -> AND u.sex = 2
  -> GROUP BY
  -> r.role_name
  -> ORDER BY
  -> r.id ASC;
+----+-----------+-----+
| id | role | sex |
+----+-----------+-----+
| 1 | Admin | 0 |
| 2 | General Manager | 2 |
| 3 | Section Chief | 1 |
| 4 | Team Leader | 2 |
+----+-----------+-----+
4 rows in set (0.00 sec)

mysql> SELECT
  -> r.id,
  -> r.role_name AS role,
  -> count( u.sex ) AS sex
  -> FROM
  -> role r
  -> LEFT JOIN USER u ON r.id = u.role_id
  -> WHERE
  -> u.sex = 2
  -> GROUP BY
  -> r.role_name
  -> ORDER BY
  -> r.id ASC;
+----+-----------+-----+
| id | role | sex |
+----+-----------+-----+
| 2 | General Manager | 2 |
| 3 | Section Chief | 1 |
| 4 | Team Leader | 2 |
+----+-----------+-----+
3 rows in set (0.00 sec)

mysql> SELECT
  -> r.id,
  -> r.role_name AS role,
  -> count( u.sex ) AS sex
  -> FROM
  -> role r
  -> LEFT JOIN USER u ON r.id = u.role_id
  -> WHERE
  -> r.role_name = 'General Manager'
  -> GROUP BY
  -> r.role_name
  -> ORDER BY
  -> r.id ASC;
+----+-----------+-----+
| id | role | sex |
+----+-----------+-----+
| 2 | General Manager | 3 |
+----+-----------+-----+
1 row in set (0.00 sec)

mysql> SELECT
  -> r.id,
  -> r.role_name AS role,
  -> count( u.sex ) AS sex
  -> FROM
  -> role r
  -> LEFT JOIN USER u ON r.id = u.role_id
  -> AND r.role_name = 'General Manager'
  -> GROUP BY
  -> r.role_name
  -> ORDER BY
  -> r.id ASC;
+----+-----------+-----+
| id | role | sex |
+----+-----------+-----+
| 1 | Admin | 0 |
| 2 | General Manager | 3 |
| 3 | Section Chief | 0 |
| 4 | Team Leader | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)