MySQL uses the left join method when the data in the right table is not unique.

MySQL uses the left join method when the data in the right table is not unique.

1. Basic left join usage

Mysql left join statement format
A left join B ON condition expression

Left join is based on table A. Table A is the left table, and table B is the right table.
All records in the left table (A) are displayed, while in the right table (B), only records meeting the condition expression are displayed. If no matching record exists in the right table (B, NULL is the place where the record is insufficient.

For example, the structure of the news and news_category tables is as follows. The category_id of the news table corresponds to the id of the news_category table.

News table

News_category table

Displays the news Table Record and news category name. The query statement is as follows:

select a.id,a.title,b.name as category_name,a.content,a.addtime,a.lastmodify  from news as a left join news_category as b  on a.category_id = b.id; 

The query result is as follows:

Because the news_category table does not have a record with id = 4, category_name = NULL for the record with category_id = 4 in the news table

When left join is used, the number of records displayed in table A and table B is or. All records in Table A are displayed, and only matching records are displayed in table B.

2. left join right table data is not unique solution

However, if the number of qualified records in Table B is greater than 1, 1: n will occur. As A result, the number of records after left join is greater than that in table.

For example, the structure of the member and member_login_log tables is as follows. member records the member information and member_login_log records the member's daily logon records. The id of the member table corresponds to the uid of the member_login_log table.

Member table

Member_login_log table

Query the member user information and the last logon date:
If left join is used directly

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

Because member_login_log has more matching records than the member table (a. id = B. uid), the final record is:

However, this is not the expected result. Therefore, we need to ensure that the qualified records of Table B are null or unique. We can use group by to implement this.

select a.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 on a.id = b.uid; 

Conclusion: The relationship between the two tables that use left join is preferably or. This ensures that all the records in Table A are displayed, while those in table B are qualified.
If the qualified records in Table B are not unique, check whether the table design is reasonable.

Articles you may be interested in:

• MySQL table examples of left join and RIGHT JOIN
• MySQL left join table JOIN tutorial
• Mysql: Can't start server: can't create PID file: No space left on device
• For MySQL Left JOIN, specify the NULL column to return specific values
• Resolve the differences between on and where filtering in mysql left (right) join
• In-depth understanding of left join in mysql
• Mysql not in, left join, is null, not exists efficiency issue records
• Usage Analysis of mysql left join, right join, and inner join