After the left connection in MySQL, the final record count is greater than the record analysis on the left table

If the number of records that meet the criteria in Table B is greater than 1, there will be a case of 1:n, so the result after the left join will be more than the number of records in Table A.

For example: The structure of member and Member_login_log table is as follows, member record the member information, Member_login_log record the member's daily log-in record. The ID of the member table is the corresponding relationship to the UID of the Member_login_log table.

Member table

Member_login_log table

Query member user's information and last login date:
If you use the LEFT join directly

selecta.id, a.username, b.logindate from member as a  left join member_login_log as b ona.id = b.uid;

Due to the Member_login_log qualifying record more than the member table (a.id = B.uid), the last recorded record is:

But this is not the result we want, so this situation needs to ensure that the qualifying records of Table B are empty or unique, and we can use Group by to implement them.

selecta.id, a.username, b.logindate from member as a  left join (select uid, max(logindate) as logindate from member_login_log group by uid) as b ona.id = b.uid;

Summary: Using the two tables of the left join, preferably a 1:1 or 1:0 relationship, so that the records of a table are all displayed, and B displays records that match the criteria.

After the left connection in MySQL, the final record count is greater than the record analysis on the left table