1.left join basic usage

mysql left join statement format

a left join b on conditional expression

left join is based on table a,Table a is the left table,Table b is the right table.

All records in the left table (a) will be displayed.The right table (b) will only show records that match the condition expression.If there are no eligible records in the right table (b),The place where the record is insufficient is null.

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

Display the news table record and the category name of the news. The query 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 results are as follows:

There are no records with id=4 in the news_category table, so category_name=null for records with category_id=4 in the news table

With left join, the number of records displayed by table a and table b is 1:1 or 1:0, and all records of table a are displayed.Table b shows only records that meet the criteria.

2.left join Right table data is not the only solution

However, if the number of eligible records in table b is greater than one, a 1:n situation will occur, so the result after left join,The number of records will be more than the number of records in table a.

For example:the structure of the member and member_login_log tables is as follows,member records member information,member_login_log records the daily login records of members.The id of the member table corresponds to the uid of the member_login_log table.

member table

member_login_log table

Query member user information and last login date:

If you use left join 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 qualified records than member table (a.id=b.uid), the resulting record is:

But this is not the result we want,Therefore, this situation needs to ensure that the eligible records in table b are empty or unique.We can use group by.

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;

Summary:two tables using left join,The relationship is preferably 1:1 or 1:0. This can ensure that all records in table a are displayed.Table b shows eligible records.

If the eligible records in table b are not unique,You need to check whether the table design is reasonable.

  • Previous How IOS sends notifications between Host App and App Extension
  • Next ASPNET data binding GridView control using skills