Resolve the differences between on and where filtering in mysql left (right) join

There is such a problem that mysql queries whether the data queried by the left (right) join filter condition on and where in mysql is different.
You may not be able to see any problems with the two keywords. There is no difference in actual examples.

For example, two table structures exist.
Table Structure 1
Copy codeThe Code is as follows:
Drop table if exists;
Create table (
ID int (1) not null,
Primary key (ID)
) ENGINE = MyISAM default charset = latin1;

Table Structure 2
Copy codeThe Code is as follows:
Drop table if exists B;
Create table B (
ID int (1) not null,
Primary key (ID)
) ENGINE = MyISAM default charset = latin1;

Table 1 insert data
Copy codeThe Code is as follows:
Insert into A values (1 );
Insert into A values (2 );
Insert into A values (3 );
Insert into A values (4 );
Insert into A values (5 );
Insert into A values (6 );

Table 2 insert data
Copy codeThe Code is as follows:
Insert into B values (1 );
Insert into B values (2 );
Insert into B values (3 );

The data in table A and table B is as follows:

Statement 1
Copy codeThe Code is as follows:
Select A. ID as AID, B. ID as BID from A left join B on A. ID = B. ID where B. ID <3

Statement 2
Copy codeThe Code is as follows:
Select A. ID as AID, B. ID as BID from A left join B on A. ID = B. ID and B. ID <3

Whether the query results of the preceding two statements are consistent.
Anyway, I didn't notice any difference between the two queries [I have never written such an SQL statement before ].
Let's look at the actual results.
Query Result of Statement 1

 

The query result of Statement 2 is:

 

 

The two queries are different.

Why is there a difference? This is related to the on query order.

We know that the execution sequence of the standard query keywords is from-> where-> group by-> having-> order by [Do you know?]

Left join is in the from range class. Therefore, the on condition filtering table is used first, and then the two tables are used for left join.

For the where clause, filter the left join result again.

The first SQL statement Query Process is equivalent:
1: left join
Copy codeThe Code is as follows:
Select A. ID as AID, B. ID as BID from A left join B on A. ID = B. ID

The query result is as follows:

2: Filter B. ID (BID) <2 in the query result.

That is, the result we see above.

The second SQL statement Query Process is equivalent:

1: first select a table based on the on condition is equivalent to filtering table B first:

2: The query result is left join with table A, which is why the SQL statement of the second query retains Table.
When using ON and where, pay attention to the following places:
(1): the filtering conditions after the ON clause are mainly for the associated tables [but not for the master table selection conditions ].
For example
Copy codeThe Code is as follows:
Select A. ID as AID, B. ID as BID from A left join B on A. ID = B. ID and A. ID = 3

The query result is

It is quite surprising that it is different from the expected results, and the data with AID = 3 is filtered out.

However, we also found that the value of AID and AID 1 in 2 is NULL, and the associated table only takes the value that meets the screening conditions of Table.

That is to say, when the primary table condition is later than on, the attached table only takes the values that meet the conditions of the master table's handsome selection, while the primary table still takes the whole table.

 (2): The filtering conditions for the primary table should be placed behind the where clause and should not be placed behind the ON clause.

 (3): we need to treat associated tables separately. If you want to connect the query item only after conditional query

Placed after ON.

If you want to filter the data after the connection is complete, place the condition after where.

 (4 ): For joined tables, we can perform subqueries and join operations first.
Therefore, the second SQL statement is equivalent
Copy codeThe Code is as follows:
Select A. ID as AID, B1.ID as BID
From A left join (select B. ID from B where B. ID <3) B1 on A. ID = B1.ID

All of the above have been tested on mysql5.1