Parse the difference between using on and where to filter mysql left(right) join_PHP tutorial

There is a question about whether there is a difference in the data queried between on and where using the left(right)join filter condition in mysql in mysql query.
You may not see any problems just by looking at the two keywords. Let's use actual examples to see if there is any difference.

For example, there are two table structures
Table structure 1

Copy code The code is as follows:

drop table if EXISTS A;
CREATE TABLE A (
ID int(1) NOT NULL,
PRIMARY KEY (ID)
) ENGINE= MyISAM DEFAULT CHARSET=latin1; The 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;

Insert data into table 1

Copy code

The 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 code

The code is as follows:


insert into B values ​​(1);

insert into B values ​​(2);
insert into B values ​​(3) ;

After completion, the data in tables A and B are as follows:

Statement 1

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

The 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

Are the query results of the above two statements consistent?
Anyway, I didn’t notice any difference between the two queries [I’ve never written SQL like this before].
Let’s take a look at the actual results
Query results of statement 1

The query result of statement 2 is:

Found a difference between the two queries.

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

We know that the execution order of standard query keywords is from->where->group by->having->order by[I don’t remember clearly]

left join is in the from range class, so the table is first filtered by the on condition, and then the left join is done on the two tables.

For where, the left join results are filtered again.

The query process of the first sql statement is as follows:
1: First left join

Copy code The 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 results are as follows

2: Filter out B.ID, that is, BID<2 in the query results.

This is the result we saw above.

The query process of the second sql statement is equivalent to:

1: First selecting the table according to the on condition is equivalent to filtering table B first:

2: Then perform a left join on the query result and table A. This is why we see that the sql of the second query retains table A.
When using ON and where, you must pay attention to the place:
(1): The filtering conditions behind ON are mainly for association tables [and for Main table brush selection conditions are not applicable].
For example

Copy code The 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 results of this for

It’s quite surprising that the result is different from what we expected, and the data with AID=3 was filtered out.

But we also found that the values ​​corresponding to AID 1 and 2 in AID and are NULL, and the associated table only takes values ​​that meet the filtering conditions of table A.

That is, when the main table condition is after on, the attached table only takes the values ​​that meet the main table selection conditions, and the main table is still a rounded table.

(2): The filtering conditions for the main table should be placed after where, not after ON

(3): We need to treat association tables differently. If you need to query conditions before connecting, you should put the query part

Place behind on.

If you want to filter after the connection is completed, you should place the condition after where

(4): For related tables, we can actually do a subquery first and then join
So the second sql is equivalent to

Copy code The 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 the above have been tested on mysql5.1

http://www.bkjia.com/PHPjc/327641.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/327641.htmlTechArticleThere is such a problem. Mysql query uses the left(right)join filter condition in mysql to query on and where. Are there any differences in the data? You may not be able to see any problems just by looking at the two keywords. ...