MySQL joint table query basic operation left-join common pitfalls

Overview

For small and medium-sized projects, joint table query is a very common operation, especially when making reports. However, when proofreading the data, did you find any pitfalls? This article reproduces common pitfalls in MySQL's commonly used joint table queries.

Basic Environment

Create table statement

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);

The data is as follows

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)

Basic business

Simple information report: Query user information

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 |
+----+-----------+--------+

Query the name of each role and the number of females in the corresponding personnel

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)

What will happen if we change the gender filtering condition to a where operation?

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)

Here you can see that the character data is incomplete.

Find the number of employees with the role of General Manager

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)

Also change the filter condition from where to on

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)

Here you can see that the data is redundant.

Summarize

In the left join statement, the left table filter must be placed in the where condition, and the right table filter must be placed in the on condition, so that the result can be just right, neither too much nor too little.

This concludes this article about the common pitfalls of left-join, a basic operation of MySQL joined table query. For more relevant MySQL joined table query left-join content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: