Left join Analysis

Document directory

• TIPS:

Case 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:
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.

Extended:

If there is no on matching in the right table, we can display the null rule to find all records in the left table, not in the right table. Note that the column to be judged must be declared as not null.
For example:
SQL:
Select ID, name, action from user as u
Left join user_action A on u. ID = A. user_id
Where a. user_id is null
(Note:1. If the column value is null, it should be "is null" instead of "= NULL ".
2. Here the. user_id column must be declared as not null)
Result:
Id | Name | action
--------------------------
3 | daodao | null

TIPS:

1. On a. C1 = B. C1 is equivalent to using (C1)
2. Inner join and (comma) are semantically equivalent.
3. When MySQL retrieves information from a table, you can prompt which index it chooses.
This feature is useful if the explain command shows that MySQL uses an index that may be incorrect in the index list.
By specifying the use index (key_list), you can tell MySQL to use the most appropriate index to find record rows in the table.
The optional syntax ignore index (key_list) can be used to tell MySQL not to use a specific index.
4. Some examples:
Mysql> select * From Table1, Table2 where table1.id = table2.id;
Mysql> select * From Table1 left join Table2 on table1.id = table2.id;
Mysql> select * From Table1 left join Table2 using (ID );
Mysql> select * From Table1 left join Table2 on table1.id = table2.id
-> Left join table3 on table2.id = table3.id;
Mysql> select * From Table1 use index (key1, key2)
-> Where key1 = 1 and key2 = 2 and key3 = 3;
Mysql> select * From Table1 ignore index (key3)
-> Where key1 = 1 and key2 = 2 and key3 = 3;

The following is an official description of the working principle and precautions of join in MySQL:

5.2.6 how MySQL optimises left join and right join

A left join B in MySQL is implemented as follows:

The table B is set to be dependent on table A and all tables that A is dependent on.
The table A is set to be dependent on all tables (tables t B) that are used in the left join condition.
All left join conditions are moved to the WHERE clause.
All standard join optimisations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error.
All standard where optimisations are done.
If there is a row in a that matches the WHERE clause, but There Wasn"
If you use left join to find rows that don't exist in some table and you have the following test: column_name is null in the where part, where column_name is a column that is declared as not null, then MySQL will stop searching after more rows (for a special key combination) after it has found one row that matches the left join condition.
Right join is implemented Analogously as left join.

The table read order forced by left join and straight join will help the join optimiser (which calculates in which order tables shoshould be joined) to do its work much more quickly, as there are fewer table permutations to check.

Note that the above means that if you do a query of type:

Select * From A, B left join C on (C. Key = A. Key) left join D (D. Key = A. Key)
Where B. Key = D. Key

MySQL will do a full scan on B as the left join will force it to be read before D.

The fix in this case is to change the query:

Select * from B, a left join C on (C. Key = A. Key) left join D (D. Key = A. Key)
Where B. Key = D. Key