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.
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.
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.
- MySQL table LEFT JOIN left join right join example tutorial
- MySQL uses profile analysis to explain slow SQL in detail (group left join is more efficient than subqueries)
- MySQL LEFT JOIN table join advanced learning tutorial
- MySQL multiple left join join query usage analysis
- Introduction to the difference between on and where placement conditions in MySQL left join operation
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- i want to call a child component method from a parent in vuejs
- python 3x - typeerror: 'method' object is not subscriptable
- dart - flutter: the instance member'stars' can't be accessed in an initializer error
- xcode - pod install [!] no `podfile 'found in the project directory