left join on 后跟and和where区别

create table testA(
  id int primary key,
  Name varchar(10)
);
insert into testA values(1, '小黄');
insert into testA values(2, '小绿'); 
insert into testA values(3, '小白');
insert into testA values(4, '小黑');
insert into testA values(5, '小花');
commit;

create table testB(
  id int primary key, 
  age int(10)
);
insert into testB values(1, 10);
insert into testB values(2, 11);
insert into testB values(3, 12);
insert into testB values(4, 13);

内连接

select * from testA t1 inner join testB t2 on t1.id=t2.id and t1.name='小白';

select * from testA t1 inner join testB t2 on t1.id=t2.id  where t1.name='小白';

 左连接

select * from testA t1 left join testB t2 on t1.id=t2.id and t1.name='小白';

select * from testA t1 left join testB t2 on t1.id=t2.id  where t1.name='小白';

其实以上结果的关键原因就是1eft join，right join，fulljoin的特殊性，不管on上的条件是否为真都会返回1eft或right表中的记录，ful1则具有1eft和right的特性的并集。而inner join 没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。on为了反映外连接中一方的全连接，而where没有这个功能，内连接配对是可以的。