Parsing MySQL Left (right) join using on vs. where filtering differences _php tutorial

There is a problem with MySQL queries that use the left (right) join filter condition in MySQL to see if there is a difference between on and where data is queried.
Maybe just looking at two keywords doesn't show any problems. Let's use the actual example to say whether there is any difference.

For example, there are two table structures
Table Structure 1
Copy CodeThe code is as follows:
drop table if EXISTS A;
CREATE TABLE A (
ID Int (1) is 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) is not NULL,
PRIMARY KEY (ID)
) Engine=myisam DEFAULT charset=latin1;

table One 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 Two Insert data
Copy CodeThe code is as follows:
INSERT into B values (1);
INSERT into B values (2);
INSERT into B values (3);

after the completion of A/b table data is as follows:

Statement One
Copy the 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 the 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 didn't notice any difference in these two queries. "I haven't written SQL before."
Let's see the actual results.
Query results for statement one

The query result for statement two is:

Two queries were found to be different.

Why there is a difference, which is 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 range class so the on condition first filters the table, and then the two tables do the left join.

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

The first SQL statement query procedure is equivalent to the following:
1: First LEFT Join
Copy the Code code as follows:
Select a.ID as AID, b.id as BID from A left join B on a.id = b.ID

The query results are as follows

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

That's what we see above.

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

1: First by the on condition to brush the table is equivalent to first filter B table:

2: Again on the query results with a table do a LEFT join, which is why we see the second query SQL will retain the a table.
On and where to use must pay attention to the place:
(1): On the following filter conditions are mainly for the association table "and for the main Table brush selection conditions do not apply."
For example
Copy the 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

It's a little surprising. Unlike the results we expect, we filter out aid=3 data.

But we also found that the aid with the aid 1 to 2 corresponds to the value of NULL, the association table only to meet the table a screen brush selection criteria.

That is, when the main table condition is on, the schedule only takes the values that satisfy the main table's handsome selection, and the main table or the whole table.

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

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

Placed after on.

If you want to filter after the connection is complete, you should put the condition behind the where

(4): for the association table we can actually do the subquery first and then join.
So a second SQL is equivalent to
Copy the 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 the above have been tested on mysql5.1.

http://www.bkjia.com/PHPjc/327641.html www.bkjia.com true http://www.bkjia.com/PHPjc/327641.html techarticle There is a problem with MySQL queries that use the left (right) join filter condition in MySQL to see if there is a difference between on and where data is queried. Maybe just looking at two keywords doesn't show any problems. ...

