1.2、Filter PushDown Cases And Outer Join Behavior

前提:关闭优化器

set hive.auto.convertjoin=false;
set hive.cbo.enable=false;

Inner Join:

1、Join On中的谓词: 左表下推、右表下推

2、Where谓词:左表下推、右表下推

-- 第一种情况: join on 谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test 1 t1
join wedw_tmp.tmp_test 2 t2
on tl.user_id = t2.user_idand tl.user_id='111' and t2.user_id='222'-- 第二种情况: where谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
join wedw_tmp.tmp_test_2 t2
on tl.user_id = t2.user_id
where t1.user_id='111' and t2.user_id='222'

Left join:

1、Join On中的谓词: 左表不下推、右表下推(前提：关闭mapjoin优化)

2、Where谓词:左表下推、右表下推

-- 第一种情况: join on 谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw tmp.tmp_test_2 t2
on tl.user_id = t2.user_id and tl.user_id='111' and t2.user_id='222'-- 第二种情况: where谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw_tmp.tmp_test 2 t2
on tl.user_id = t2.user_id
where t1.user_id='111' and t2.user_id='222'

Right Join:

1、Join On中的谓词: 左表下推、右表不下推(前提：关闭mapjoin优化)

2、Where谓词:左表下推、右表下

-- 第一种情况: join on 谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id and t1.user_id='111' and t2.user_id='222'--第二种情况: where谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2on t1.user_id = t2.user_id
where t1.user_id='111' and t2.user_id='222'

Full Join:

1、Join On中的谓词: 左表不下推、右表不下推(前提：关闭mapjoin优化)

2、Where谓词:左表不下推、右表不下推

-- 第一种情况: join on 谓词
selectt1.user_id,t2.user id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test 2 t2on t1.user_id = t2.user_id and tl.user_id='111' and t2.user_id='222'-- 第二种情况: where谓词
selectt1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2 .user_id
where tl.user_id='111' and t2.user_id='222'

总结：