use dmp;
create table dmp.yl_test_1(id int,name string);
create table dmp.yl_test_2(id int,age int);
insert into dmp.yl_test_1 values(1,'aa');
insert into dmp.yl_test_1 values(2,'bb');
insert into dmp.yl_test_1 values(3,'cc');
insert into dmp.yl_test_2 values(1,40);
insert into dmp.yl_test_2 values(2,50);
条件放在on和where后面的区别:
表1
id,name
1,aa
2,bb
3,cc
表2
id,num
1,40
2,50
select * from dmp.yl_test_1 t1
left join
dmp.yl_test_2 t2 on t1.id = t2.id and t1.id = 3;
id name id_1 age
1 aa
2 bb
3 cc
select * from dmp.yl_test_1 t1
left join
dmp.yl_test_2 t2 on t1.id = t2.id where t1.id = 3;
id name id_1 age
3 cc
select * from dmp.yl_test_1 t1
left join
dmp.yl_test_2 t2 on t1.id = t2.id and t2.id = 3;
id name id_1 age
1 aa
2 bb
3 cc
select * from dmp.yl_test_1 t1
left join
dmp.yl_test_2 t2 on t1.id = t2.id where t2.id = 3;
id name id_1 age
一、对于left join,如果在on上写a表的条件,则该条件不会生效,依旧会全局扫描
二、对于left join,在on上写副表b的条件会生效,但是语义与写到where 条件不同
三、对于inner join 在on上写主表a或者副表b的条件,都会生效,和写在where里结果一样
参考