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

Priority

The reason why placing the same condition in both cases may result in different result sets is because of the 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 the database returns records by connecting two or more tables, it generates an intermediate temporary table and then returns this temporary table to the user.

In left join, the difference between the two is:

• on is the condition used when generating a temporary table. Regardless of whether the on condition works or not, the rows of the left table (table_name1) will be returned.
• Where is the condition used after the temporary table is generated. At this time, it doesn’t matter whether left join is used or not. All rows that do not meet the condition will be filtered out.

test

Table 1: table1

Table 2: table2

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';

First result set:

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

Second result set:

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

The execution process of the first SQL statement is: first find the row in table b where name is aaa (on (a.No = b.No and b.name='aaa') ). Then find the data of table a (even if it does not meet the rules of table b), generate a temporary table and return it to the user.

The execution process of the second SQL statement is as follows: first, a temporary table is generated, then the where statement is executed to filter the result set where b.name='aaa' is not true, and finally the result is returned to the user.

Because on will first filter out rows that do not meet the conditions, and then perform other operations, it stands to reason that on is the fastest.

When querying multiple tables, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the join conditions between the tables, then filters it using where, and then calculates it. After the calculation, it is filtered again using having. It can be seen from this that in order for the filtering conditions to play a correct role, we must first understand when the condition should take effect, and then decide where to put it.

For the association operation of the tables involved in JOIN, if the rows that do not meet the connection conditions are required to be within our query range, we must put the connection conditions after ON, not after WHERE. If we put the connection conditions after WHERE, then all LEFT, RIGHT, etc. operations will not have any effect. In this case, its effect is exactly the same as INNER connection. 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 the previous LEFT and RIGHT associations will be used as decoration and will not have any effect.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.