In mysql, we can directly use the left join statement to implement left join. Let's see how to use left join to implement left join for two tables.
First look at the instance
The Code is as follows: |
Copy code |
SELECT p. price, pd. name, m. manufacturers_name FROM (products p Left join products_description pd ON p. products_id = pd. products_id) Left join manufacturers m ON p. manufacturers_id = m. manufacturers_id |
A note ON "a left join B ON conditional expression"
ON condition (ON in "a left join B on condition expression") is used to determine how to retrieve data rows from Table B.
If no row of data in Table B matches the ON condition, an additional row of data with all columns being NULL is generated.
None of the WHERE clause conditions in the matching phase will be used. The WHERE clause condition is used only after the matching stage is complete. It will retrieve and filter the data generated in the matching phase.
Joint Query Efficiency Analysis
User table:
Id | name
---------
1 | libk
2 | zyfon
3 | daodao
User_action table:
User_id | action
---------------
1 | jump
1 | kick
1 | jump
2 | run
4 | swim
SQL:
The Code is as follows: |
Copy code |
Select id, name, action from user as u Left join user_action a on u. id = a. user_id |
Result:
Id | name | action
--------------------------------
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤
Analysis:
Note that user_action has a user_id = 4, action = swim record, but it does not appear in the result,
In the user table, the id = 3 and name = daodao users do not have corresponding records in user_action, but they appear in the result set.
Because it is left join, all work is subject to left.
Result 1, 2, 3, 4 are records in both the left table and the right table. 5 is a record in only the left table, not in the right table.
Conclusion:
We can imagine that left join works like this.
Read one record from the left table and select all records (n records) in the right table that match on to Form n records (including duplicate rows, for example: result 1 and result 3 ),
If there is no table matching the on condition on the right side, all connected fields are null.
Then read the next one.