原理：
關聯(lián)時會先創(chuàng)建臨時表t1和t2，where后面的條件會先過濾t1诉稍、t2臨時表后在關聯(lián)，on后面的條件會先關聯(lián)t1最疆、t2后在過濾杯巨。
結論：
條件在on后面，主表數(shù)據(jù)量不變努酸，副表只顯示符合條件的服爷。
條件在where后面，先過濾條件在關聯(lián)获诈。
測試時直接替換dmp庫就可以執(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
