The difference between where and on conditions in MySQL left join

A few knowledge points about where and on conditions in the left join: 1. Multiple table LEFT join is a temporary table that is generated and returned to the user 2.where condition is filtered for the last generated temporary table, filtering out records that do not meet the Where condition.    is really non-conforming on the filter out. The 3.on condition is conditional filtering on the right table of the left join, but still returns all rows from the table in the right, and the fill null 4.on condition in the table without the left table condition, which returns all rows of the left table regardless of the true or false condition, but affects the matching value of the right table. That is, the constraint on the left table in on is only affecting the matching contents of the right table, and does not affect the number of rows returned. Conclusion: The limit of the left table in the 1.where condition can not be put on the right table in the 2.where condition after on, and the data row number will be different than the original number of rows. Create two tables: Creation table T1 (ID int,name VARCHAR (20) INSERT INTO ' T1 ' (' id ', ' name ') of values (1, ' A11 '); insert INTO ' T1 ' (' id ', ' name ') VALUES (2, ' A22 '); insert INTO ' T1 ' (' id ', ' Name ') VALUES (3, ' A33 '); insert INTO ' T1 ' (' id ', ' name ') VALUES (4, ' A44 '); CREATE TABLE T2 (id int,local VARCHAR), insert INTO ' T2 ' (' id ', ' local ') VALUES (1, ' Beijing '); insert INTO ' T2 ' (' id ', ' loc Al ') VALUES (2, ' Shanghai '); insert INTO ' T2 ' (' id ', ' local ') VALUES (5, ' Chongqing '); insert INTO ' T2 ' (' id ', ' local ') VALUES ( 6, ' Tianjin '); Test 01: Returns all rows of the left table, the right table matches the on condition, does not satisfy the condition of the Null[email protected]:cuigl 11:04:25 >select t1.id,t1.name , t2.local from T1 left joins T2 on t1.id=t2.id;+------+------+----------+| ID | name |    Local |+------+------+----------+| 1 | A11 |    Beijing | | 2 | A22 |    Shanghai | | 3 | A33 |    NULL | | 4 | A44 | NULL |+------+------+----------+4 rows in Set (0.00 sec) Test 02:on add a constraint to the right table: t2.local= ' Beijing ' conclusion 02: Left Table records all return, right table filter conditions take effect [Email protected]:cuigl 11:19:42 >select t1.id,t1.name,t2.local from T1 left JOIN T2 on t1.id=t2.id and t2.local= ' Beijing '; +------+------+---------+| ID | name |    Local |+------+------+---------+| 1 | A11 |    Beijing | | 2 | A22 |    NULL | | 3 | A33 |    NULL | | 4 | A44 | NULL |+------+------+---------+4 rows in Set (0.00 sec) Test 03: Adds a restriction to the right table only after the Where: t2.local= ' Beijing ' conclusion 03: For the right table, the same condition, After where the last temporary table is filtered by record, the number of rows may be reduced, and after on is filtered as a matching criterion, the contents of the right table are filtered. [Email protected]:cuigl 11:20:07 >select t1.id,t1.name,t2.local from T1 left JOIN T2 on T1.id=t2.id where t2.local   = ' Beijing '; +------+------+---------+| ID | name |    Local |+------+------+---------+| 1 | A11 | BEIJING |+------+------+---------+1 Row in Set (0.01 sec) test 04:t1.name= ' A11 ' or t1.name= ' a33 ' conclusion 04:on restrictions on left table do not affect the number of rows returned, only the matching contents of the right table are affected [email   Protected]:cuigl 11:24:46 >select t1.id,t1.name,t2.local from T1 left joins T2 on T1.id=t2.id and t1.name= ' A11 '; +------+------+---------+| ID | name |    Local |+------+------+---------+| 1 | A11 |    Beijing | | 2 | A22 |    NULL | | 3 | A33 |    NULL | | 4 | A44 | NULL |+------+------+---------+4 rows in Set (0.00 sec) [Email protected]:cuigl 11:25:04 >select t1.id,t1.name,t 2.local from T1 left joins T2 on T1.id=t2.id and t1.name= ' a33 '; +------+------+-------+| ID | name |    Local |+------+------+-------+| 1 | A11 |    NULL | | 2 | A22 |    NULL | | 3 | A33 |    NULL | | 4 | A44 | NULL |+------+------+-------+4 rows in Set (0.00 sec) Test 05:where t1.name= ' A33 ' or where t1.name= ' A22 ' conclusion 05:where condition is in the last temporary table Based on a filter that shows only rows that meet the last where condition [Email protected]:cuigl 11:25:15 >select t1.id,t1.name,t2.local from T1 left JOIN T2 on T1.id=t2.id where T1.name= 'A33 '; +------+------+-------+| ID | name |    Local |+------+------+-------+| 3 | A33 | NULL |+------+------+-------+1 row in Set (0.00 sec) [Email protected]:cuigl 11:27:27 >select T1.id,t1.name, T2.local from T1 left JOIN T2 on t1.id=t2.id where t1.name= ' a22 '; +------+------+----------+| ID | name |    Local |+------+------+----------+| 2 | A22 | Shanghai |+------+------+----------+1 row in Set (0.00 sec)

　　

The difference between where and on conditions in MySQL left join