Parsing the difference _php techniques for MySQL Left (right) join using on and where filtering

There's a problem with this MySQL query uses the right join filter in MySQL to see if there is a discrepancy between on and where the data is queried.
Maybe just look at two keywords and see no problem. So we use actual examples to see if there's any difference.

For example, there are two table structures
Table Structure 1

Copy Code code as follows:

drop table if EXISTS A;
CREATE TABLE A (
ID Int (1) Not NULL,
PRIMARY KEY (ID)
) Engine=myisam DEFAULT charset=latin1;

Table Structure 2

Copy Code code as follows:

drop table if EXISTS B;
CREATE TABLE B (
ID Int (1) Not NULL,
PRIMARY KEY (ID)
) Engine=myisam DEFAULT charset=latin1;

Table One Insert data

Copy Code code 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 II inserting data

Copy Code code as follows:

INSERT into B values (1);
INSERT into B values (2);
INSERT into B values (3);

after the completion of the A,B table data are as follows:

Statement One

Copy Code code 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 Two

Copy Code code 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

The query results for the above two statements are consistent.
Anyway I'm not aware of any differences in these two queries "have never written SQL before."
Let's look at the actual results.
Query results for statement one

The query result for statement two is:

Two queries were found to be different.

Why there are differences, this and on are related to the where query order.

We know that the standard query keyword execution order is from->where->group By->having->order by[remember not very clearly?

The left join is in the From scope class, so the first on condition filters the table, then the two tables do the left join.

And for where, the left join results are filtered again.

The first SQL statement query process is equivalent to the following:
1: First LEFT Join

Copy Code code as follows:

Select a.ID as AID, b.id as BID from A left join B on a.id = b.ID

Query results are as follows

2: The query results will be b.id that bid<2 screening out.

That's the result we see above.

The second SQL statement query process is equivalent to the following:

1: According to the on condition Brush Select table is equivalent to filter B table first:

2: Again the query result and a table do left join, which is why we see the second query SQL will retain a table for the reason.
On and where use must be noted:
(1): On the following filter criteria are mainly for the association table "and for the main Table brush selection conditions do not apply."
For example

Copy Code code 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 result of this query is

I'm surprised. It is not the same as the results we expected, and to filter out the aid=3 data.

But we also found that the corresponding value of aid and medium aid 1 to 2 is null, and the correlation table only takes the values that satisfy the table a screen brush selection condition.

That is, when the primary table condition is on, the schedule takes only the value that satisfies the selection criteria for the main table, and the primary table or the whole table.

(2): The filter criteria for the primary table should be placed behind the where, should not be placed on the back

(3): We have to differentiate between related tables. If you want to conditional query before the connection should be the query pieces

Placed on after.

If you want the connection to be completed before filtering, you should put the condition behind the where

(4): For the association table we can actually do the subquery first and then join
So the second SQL is equivalent to

Copy Code code 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 these were tested on mysql5.1.