原理：
關(guān)聯(lián)時(shí)會(huì)先創(chuàng)建臨時(shí)表t1和t2，where后面的條件會(huì)先過(guò)濾t1、t2臨時(shí)表后在關(guān)聯(lián)，on后面的條件會(huì)先關(guān)聯(lián)t1、t2后在過(guò)濾。
結(jié)論：
條件在on后面，主表數(shù)據(jù)量不變，副表只顯示符合條件的。
條件在where后面，先過(guò)濾條件在關(guān)聯(lián)。
測(cè)試時(shí)直接替換dmp庫(kù)就可以執(zhí)行
-- 條件放在on和where后面的區(qū)別：
use tmp;
drop table tmp.yl_test_1;
drop table tmp.yl_test_2;
create table tmp.yl_test_1(id int,name string,birthday string);
create table tmp.yl_test_2(id int,age int,birthday string);
insert into tmp.yl_test_1 values(1,'aa','2023-12-01');
insert into tmp.yl_test_1 values(2,'bb','2023-12-12');
insert into tmp.yl_test_1 values(3,'cc','2023-12-30');
insert into tmp.yl_test_2 values(1,40,'2023-12-04');
insert into tmp.yl_test_2 values(2,50,'2023-12-10');
insert into tmp.yl_test_2 values(2,50,'2023-12-20');
select * from tmp.yl_test_1 t1;
id name birthday
1 aa 2023-12-01
2 bb 2023-12-12
3 cc 2023-12-30
select * from tmp.yl_test_2 t1;
id age birthday
1 40 2023-12-04
2 50 2023-12-10
2 50 2023-12-20
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id;
id name birthday id2 age birthday2
2 bb 2023-12-12 2 50 2023-12-10
2 bb 2023-12-12 2 50 2023-12-20
1 aa 2023-12-01 1 40 2023-12-04
3 cc 2023-12-30 \N \N \N
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id and t1.id = 3;
id name birthday id2 age birthday2
1 aa 2023-12-01 \N \N \N
2 bb 2023-12-12 \N \N \N
3 cc 2023-12-30 \N \N \N
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id where t1.id = 3;
id name birthday id2 age birthday2
3 cc 2023-12-30 \N \N \N
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id and t2.id = 3;
id name birthday id2 age birthday2
1 aa 2023-12-01 \N \N \N
2 bb 2023-12-12 \N \N \N
3 cc 2023-12-30 \N \N \N
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id where t2.id = 3;
id name birthday id2 age birthday2
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id and t1.birthday > t2.birthday;
id name birthday id2 age birthday2
3 cc 2023-12-30 \N \N \N
2 bb 2023-12-12 2 50 2023-12-10
1 aa 2023-12-01 \N \N \N
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id where t1.birthday > t2.birthday;
id name birthday id2 age birthday2
2 bb 2023-12-12 2 50 2023-12-10
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id and t1.birthday < t2.birthday;
id name birthday id2 age birthday2
3 cc 2023-12-30 \N \N \N
2 bb 2023-12-12 2 50 2023-12-20
1 aa 2023-12-01 1 40 2023-12-04
select * from tmp.yl_test_1 t1
left join
tmp.yl_test_2 t2 on t1.id = t2.id where t1.birthday < t2.birthday;
id name birthday id2 age birthday2
2 bb 2023-12-12 2 50 2023-12-20
1 aa 2023-12-01 1 40 2023-12-04
