left join加上on条件与where条件区别

create table a
(id int,name varchar(100))

insert a
select 1,'a'
union
select 2,'b'
union
select 3,'c'

create table b
(id int,name varchar(100))

insert into b
select 1,'1'
union
select 1,'1111'
union
select 2,'2'
union
select 2,'2222'

select a.*,b.*
from a  left join b on a.id=b.id
where a.name='a'

select a.*,b.*
from a  left join b on a.id=b.id
where b.name='1111'
结果

select a.*,b.*
from a  left join b on a.id=b.id and a.name='a'

结果
id      name    id      name
1 a 1 1
1 a 1 1111
2 b NULL NULL
3 c NULL NULL

select a.*,b.*
from a  left join b on a.id=b.id and a.name='d'

结果
id      name    id      name
1 a NULL NULL
2 b NULL NULL
3 c NULL NULL

select a.*,b.*
from a  left join b on a.id=b.id  and b.name='1'

结果
id      name    id      name
1 a 1 1
2 b NULL NULL
3 c NULL NULL

select a.*,b.*
from a  left join b on a.id=b.id and b.name='3'

结果
id      name    id      name
1 a NULL NULL
2 b NULL NULL
3 c NULL NULL