1.left Join Basic usage
MySQL LEFT JOIN statement format
A left JOIN B an on condition expression
The left join is based on a table, table A is the left-hand table, and B is the right table.
The records for left table (A) are all displayed, and the right table (b) only displays records that match the criteria expression, and if there are no records in the right table (b) that are not eligible, the record is null.
For example: The structure of the news and News_category tables is as follows, and 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 displays the category name of news, with the following query statement
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 results of the query are as follows:
Because the News_category table has no id=4 records, the Category_id=4 records in the news table Category_name=null
With a LEFT join, all records in Table A and B show 1:1 or 1:0,a tables, and Table B displays only those records that match the criteria.
2.left Join right table data not unique solution
However, if the B table matches the number of records that are greater than 1, there will be 1:n, so that the result of the left join is more than the number of records in Table A.
For example: The structure of member and Member_login_log table is as follows, members record membership information, Member_login_log record member's Daily login record. The ID of the member table corresponds to the UID of the Member_login_log table.
Member table
Member_login_log table
For information on member users and last login date:
If you use the LEFT join directly
Select a.ID, A.username, b.logindate from the as
a left
join Member_login_log as B on a.id = B.uid;
Because the Member_login_log qualifying records are more than the member table (a.id = B.uid), the resulting record is:
But this is not the result we want, so this situation requires that the record of the qualifying records for Table B be empty or unique, and we can use Group by to implement it.
Select a.ID, A.username, b.logindate from the as
a left
join (select UID, max (logindate) as Logindate Er_login_log GROUP by UID) as B on
a.id = B.uid;
Summary: Use the two tables of the left join, preferably 1:1 or 1:0, to ensure that the records for table A are all displayed, and table B shows the records that match the criteria.
If the table B meets the criteria of the record is not unique, you need to check whether the table design is reasonable.