Shulou(Shulou.com)05/31 Report--

This article is about the differences between on and where placement conditions in MySQL left join operations. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Priority

The reason why the two put the same condition may lead to different result sets is because of the priority. On takes precedence over where.

First of all, two concepts are clarified:

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 a record by joining two or more tables, it generates an intermediate temporary table, and then returns the temporary table to the user.

Under left join, the difference between the two is:

On is the condition used when generating a temporary table, and the row of the left table (table_name1) is returned regardless of whether the condition of on works or not.

Where is the condition used after the temporary table is generated, regardless of whether left join is used or not, as long as the condition is not true, filter it all.

test

Table 1:table1

IdNo1n12n23n3

Table 2:table2

Nonamen1aaan2bbbn3cccselect a.idrecovera. On b.name from table1 a left join table2 b on (a.No = b.No and b.namestamentooaaaaa'); select a.idrect a.Nowery b.name from table1 a left join table2 b on (a.No = b.No) where b.namebadooaaaaaa'

First result set:

| | id | No | name | |-- | | 1 | N1 | aaa | | 2 | N2 | (Null) | | 3 | N2 | (Null) |

Second result set:

| | id | No | name | |-| | 1 | N1 | aaa |

The execution flow of the first sql: first find the record row of table b whose name is aaa (on (a.No = b.No and b.nameplate records aaaa'). 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 to the user.

The execution flow of the second sql: first generate the temporary table, then execute where to filter the result set of b. Name _ indexaaaa 'that is not true, and finally return it to the user.

Because on first filters out rows that do not meet the criteria before performing other operations, on is supposed to be the fastest.

On works earlier than where in multi-table queries. First of all, according to the join conditions between the tables, the system combines several tables into a temporary table, then filters it by where, then calculates it, and then filters it by having after calculation. Thus, for the filter condition to play the right role, we must first understand when the condition should work, and then decide where to put it.

For the associated operation of the table that JOIN participates in, if the rows that do not meet the join conditions are also within the scope of our query, we must put the join conditions after ON, not after WHERE. If we put the join conditions after WHERE, then all LEFT,RIGHT, and other operations will not have any effect, for this case, its effect is exactly the same as INNER join. For those conditions that do not affect the selection of rows, put them after ON or WHERE.

Remember: all connection conditions must be placed after ON, otherwise all previous LEFT and RIGHT associations will be used as devices without any effect.

Thank you for reading! This is the end of this article on "what are the differences between on and where placement conditions in MySQL left join operation?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.