MySQL left/right join on and where minor differences

Always in the leak out of the bug, only to find that they do not know the depth of knowledge.

There are two table structures

drop table if EXISTS A; CREATE TABLE A (  ID int (1) not NULL,  PRIMARY KEY  (ID)) Engine=myisam DEFAULT Charset=utf8;

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);

drop table if EXISTS B; CREATE TABLE B (  ID int (1) not NULL,  PRIMARY KEY  (ID)) Engine=myisam DEFAULT Charset=utf8;

Insert into B values (1), insert into B values (2), insert into B values (3);

Sql1:select a.id as AID, b.id as BID from A left join B on a.id = b.id where b.id<3

sql2:select a.id as AID, b.id as BID from A left join B on a.id = b.id and b.id<3

The result is not the same.

SQL1 results:

SQL2 results:

on and where to use precautions :

(1): On the following filter conditions are mainly for the association table "and for the main Table brush selection conditions do not apply", The main table condition on the back of the schedule only to meet the main table handsome selection criteria, and the main table or take 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 a conditional query after the connection should be placed on the query pieces. If you want to filter after the connection is complete, you should put the condition behind the where

MySQL left/right join on and where minor differences