left join 左连接
1 with temp1 as ( 2 select 1 as id ,12 as num union 3 select 2 as id ,13 as num union 4 select 3 as id ,12 as num union 5 select 4 as id ,14 as num union 6 select 5 as id ,12 as num union 7 select 6 as id ,16 as num 8 ), temp2 as ( 9 select 1 as id ,‘a‘ as var union 10 select 2 as id ,‘b‘ as var union 11 select 3 as id ,‘c‘ as var union 12 select 4 as id ,‘a‘ as var union 13 select 5 as id ,‘b‘ as var union 14 select 6 as id ,‘f‘ as var 15 ) 16 select * from temp1 t1 left join temp2 t2 on t1.id =t2.id
left join 左连接 （on 中加条件）
说明：先关联， 再过滤 ；
1 with temp1 as ( 2 select 1 as id ,12 as num union 3 select 2 as id ,13 as num union 4 select 3 as id ,12 as num union 5 select 4 as id ,14 as num union 6 select 5 as id ,12 as num union 7 select 6 as id ,16 as num 8 ), temp2 as ( 9 select 1 as id ,‘a‘ as var union 10 select 2 as id ,‘b‘ as var union 11 select 3 as id ,‘c‘ as var union 12 select 4 as id ,‘a‘ as var union 13 select 5 as id ,‘b‘ as var union 14 select 6 as id ,‘f‘ as var 15 ) 16 -- select * from temp1 t1 left join temp2 t2 on t1.id =t2.id 17 select * from temp1 t1 left join temp2 t2 on t1.id =t2.id and t1.num=12 18 -- select * from temp1 t1 left join temp2 t2 on t1.id =t2.id where t1.num=12
left join 左连接 （末尾 where 条件）
说明：最后过滤
1 with temp1 as ( 2 select 1 as id ,12 as num union 3 select 2 as id ,13 as num union 4 select 3 as id ,12 as num union 5 select 4 as id ,14 as num union 6 select 5 as id ,12 as num union 7 select 6 as id ,16 as num 8 ), temp2 as ( 9 select 1 as id ,‘a‘ as var union 10 select 2 as id ,‘b‘ as var union 11 select 3 as id ,‘c‘ as var union 12 select 4 as id ,‘a‘ as var union 13 select 5 as id ,‘b‘ as var union 14 select 6 as id ,‘f‘ as var 15 ) 16 -- select * from temp1 t1 left join temp2 t2 on t1.id =t2.id 17 -- select * from temp1 t1 left join temp2 t2 on t1.id =t2.id and t1.num=12 18 select * from temp1 t1 left join temp2 t2 on t1.id =t2.id where t1.num=12
left join 左连接 （ on 中加条件 & 末尾 where 条件）
1 with temp1 as ( 2 select 1 as id ,12 as num union 3 select 2 as id ,13 as num union 4 select 3 as id ,12 as num union 5 select 4 as id ,14 as num union 6 select 5 as id ,12 as num union 7 select 6 as id ,16 as num 8 ), temp2 as ( 9 select 1 as id ,‘a‘ as var union 10 select 2 as id ,‘b‘ as var union 11 select 3 as id ,‘c‘ as var union 12 select 4 as id ,‘a‘ as var union 13 select 5 as id ,‘b‘ as var union 14 select 6 as id ,‘f‘ as var 15 ) 16 select * from temp1 t1 left join temp2 t2 on t1.id =t2.id and t1.num=12 where t1.num=12