The usage of the on where has in the MySQL database about inner joins and external links. Reproduced

differences between on and where conditions in SQL

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose there are two tables:

Table 1:TAB2

Id	Size
1	10
2	20
3	30

Table 2:TAB2

Size	Name
10	Aaa
20	Bbb
20	Ccc

Two sql:1, select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '

2. Select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL procedure: 1. Intermediate table on condition: Tab1.size = tab2.size Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa 2 20 20 Bbb 2 20 20 Ccc 3 30 (NULL) (NULL) | | 2, again on the intermediate table filter where Condition: tab2.name= ' AAA ' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

Second SQL procedure: 1, intermediate table on condition: Tab1.size = tab2.size and Tab2.name= ' AAA '   (the condition is not true also returns records from the left table) tab1. ID tab1.size tab2.size tab2.name 1 ten ten AAA 2 (null) (null) 3 (null) (null)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. and inner jion does not have this particularity, the condition is placed in and where, the result set returned is the same.

It can be understood that on is a function of generating a join table, where the connection table is then filtered after the connection table is generated.

When a LEFT join is used, all records of the table are returned, regardless of whether the on condition is satisfied, the records of the two tables are concatenated for the records that satisfy the criteria, and the right table field is null for records that do not meet the criteria.

When using the right join, it's just like all the records that return all the tables

When using inner join, the function is exactly the same as where.

Further deepening the understanding of on and where, the following conclusions are drawn:

0. After the on condition if there is a condition to filter the main table, the result will retain the original number for the main table data that does not match the condition, except that the right table data is not matched. For the filter condition on the right table after on, the connection will filter the right table data directly and then the left connection. In summary, for data that does not satisfy all the conditions on the back, the left table retains data in the number of results data, but does not match the right table data. The fields of the right table data that do not meet the criteria are connected directly to the main table with NULL.

The filter for left table after 1.ON is ignored for the result row count, but it affects the matching right table data in the result, because only data that conforms to the left table condition will go to match the right table data that matches the condition, and the left table data that does not match the condition will remain in the final result. However, the matching right table data is null. Therefore, for the need to filter the left table data, you need to put the filter conditions behind where.

The left table condition after 2.ON (the filter for the left table alone) has no effect on the number of result rows or returns all the data from the left table, but when the right table matches the data, The system will only take the data of the left table to match the condition (on the left Table filter condition) and fetch the data with the right table matching condition (on the right Table filter condition), while the non-conforming left table data will appear in the result list, but the corresponding right table data is null.

The right table condition after 3.ON (the filter for the right table alone) will first filter the right table and then make a connection query to the left table. has an effect on the number of result rows (when left table is a couple of days to the right table), but does not affect the number of rows displayed on the left table, and then matches the right table data to match the condition's left table data.

4.Where or filter the data after the connection, this is no objection.

5. The matching data, regardless of the left and right table, is to match on the filter conditions to do data matching, non-compliant will retain the left table data, with NULL to fill the table data.

In conclusion, the filter condition on the left table after on is ignored in the last result row number, and the left table data will not be filtered first, but the right table condition on the right will first filter the table data and then join the left table for querying.

When connecting the query, it is the data that conforms to the filter condition of the left and right table to connect the query, only the data that conforms to the right and left table filter condition can match correctly, the remaining left table data will appear normally in the result set, but the matching data is null. Therefore, for the filter condition of the left table, remember to put in where, for the right table filter condition depends on the situation. If you need to filter the right table data first, put the condition on the back.

On, where, have the difference between, where, having these three clauses that can be added conditionally, on is the first execution, where second, having the last. Sometimes if this order does not affect the intermediate results, then the final result is the same.                   But because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to deal with the data, it should be said that the speed is the fastest. According to the above analysis, you can know where should also be faster than having, because it filters the data before the sum, so having is the slowest.         But it is not that having a useless, because sometimes in step 3 did not come out of the record is not known to meet the requirements, it is necessary to use having. It is used on when two tables are joined, so there is a comparison between where and having a table.          In the case of this single-table query statistic, if the conditions to be filtered are not related to the fields to be computed, then the results are the same, except where the Rushmore technique can be used, and the having is not, the latter is slower in speed.          If a calculated field is to be involved, it means that the value of the field is indeterminate until it is calculated, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated before it works, so in this case the results will be different. On a multi-table join query, on has an earlier effect 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 it there

The usage of the on where has in the MySQL database about inner joins and external links. Reproduced