Three points you may need to know about MySQL left JOIN

1) The LEFT join is the right

Even if you think you have a deep understanding of MySQL's left JOIN, I bet this article will make you learn something!

• The ON clause differs from the WHERE clause
• A better understanding with WHERE ... A simple method for complex matching conditions of the IS NULL clause
• The difference between matching-conditions and where-conditions

A little reminder of "a left JOIN B on conditional expression"

the On condition ("a left JOIN B in conditional expression" on) is used to determine how data rows are retrieved from table B.

If none of the rows in table B match The on condition, an additional row will be generated for all columns with NULL data

The condition of the where clause in the match phase is not used. The WHERE clause condition is used only after the match phase is complete. It retrieves the filter from the data that is produced during the matching phase.

Test

Table 1:table2

ID	No
1	N1
2	N2
3	N3

Table 2:table2

no	name
n1	aaa
n2	bbb
n3	CCC

select A.id,a.no,b.name from table1 a  Left join table2 b on (A.No = b.< Span class= "Hljs-keyword" >no and b.name= ' aaa '); select a.id,a.no,b.name from table1 a left join table2 b Span class= "Hljs-keyword" >on (A.no = B.no) where b.name= ' AAA ';         

• 1
• 2

• 1
• 2

First result set:

|id |No |name||---|---|---||1  |n1 |aaa||2 |n2 |(Null)||3 |n3 |(Null)| 

• 1
• 2
• 3
• 4
• 5

• 1
• 2
• 3
• 4
• 5

A second result set:

|id |No |name||---|---|---||1  |n1 |aaa|

• 1
• 2
• 3

• 1
• 2
• 3

The execution flow of the first SQL: first to find the record row of name AAA for Table B (On (a.no = b.no and B.name= ' AAA ')). Then find the data of a (even if it does not conform to the rules of Table B) and generate a temporary table to return the user.
The second SQL execution flow: first generate a temporary table, and then execute where filter b.name= ' AAA ' is not a true result set, and finally returned to the user.

Because on will filter out non-conforming rows first, then other operations will be performed, so on is arguably the quickest.

On multi-table queries, on is more effective than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put there.

For associative operations on tables joined by joins, if a row that does not satisfy the join condition is also within our query, we must put the join condition on the back, not in the where, if we put the join condition behind the where, then all the Left,right, Such operations will have no effect, and in this case, the effect is exactly the same as the inner connection. For those conditions that do not affect the selection of rows, either on or behind.

Remember: All connection conditions must be placed behind, otherwise all left, and right will be used as a device, without any effect.

Three points you may need to know about MySQL left JOIN