Introduction of the difference between on and where placement conditions in MySQL left join operation

Priority

The reason why the two places the same condition may lead to different result sets is because of priority. The priority of on is higher than that of where.

First, clarify two concepts:

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2). When a database returns records by concatenating two or more tables, it generates a temporary table in the middle, which is then returned to the user.

Under left join, the difference between the two is:

on is the condition used when generating the temporary table, and the rows of the left table (table_name1) are returned regardless of whether the condition of on works or not. where is the condition used after generating the temporary table. At this time, no matter whether left join is used or not, as long as the condition is not true, all rows are filtered out.

Test

Table 1: table1

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.No and b.name='aaa');
select a.id,a.No,b.name from table1 a left join table2 b on (a.No = b.No) where b.name='aaa';

The first result set:

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

The second result set:

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

Execution flow of the first sql: First, find the record row in the b table where name is aaa (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 b table) and generate a temporary table to return to the user.

The second sql execution process: First generate a temporary table, then execute where to filter the result set that b. name = 'aaa' is not true, and finally return it to the user.

Because on filters out unqualified rows before performing other operations, on is arguably the fastest.

In multi-table queries, on works earlier than where. According to the join condition of each table, the system synthesizes several tables into a temporary table, and then filters it by where, then calculates it, and filters it by having after calculation. Thus, in order for the filter condition to work correctly, you must first understand when the condition should work, and then decide where to put it.

For the associated operations of the tables in which JOIN participates, If we need rows that do not meet the join condition to be in our query scope, we must put the join condition after ON, not after WHERE. If we put the join condition after WHERE, then all LEFT, RIGHT, etc. will have no effect. In this case, its effect is exactly the same as INNER join. For those conditions that do not affect the selection of rows, just put them after ON or WHERE.

Remember: All connection conditions must be placed after ON, otherwise all previous LEFT, associated with RIGHT, will be used as decoration and have no effect.

Summarize