hive中left/right join on連接中and與where的使用問題 ...
很多同學在進行表關聯的時候，and和where的使用有時候分不清，在這裡實操記錄下。
創建人員信息表並寫入數據
create table tmp.userinfo
(
id string,
name string,
age string,
dept string
);
insert into tmp.userinfo values ('1','張三','12','1'),('2','張三1','223','2'),('3','張三2','23','3'),('4','張三3','243','4'),('5','張三4','523','5');
創建部門表並寫入數據
create table tmp.deptinfo
(
id string,
name string
);
insert into tmp.deptinfo values('1','1'),('2','2'),('3','3'),('4','4'),('5','5'),('6','6')
-
現象
查詢語句 select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id and u.age<'23';
結果如下：
查詢語句 select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id where u.age<'23';
結果如下：
-
解釋
在join中on是起到關聯關係的連接作用，on後面指定的是關聯條件，比如select * from A a left join B b on a.clientno=b.clientno 就是當兩張表clientno相同才能關聯上，
如果後面再跟上and的話，就是要同時滿足這兩個條件，如 select * from A a left join B b on a.clientno=b.clientno and a.sex=b.sex，其實這個可以將on後面看成一個整體：select * from A a left join B b on (a.clientno=b.clientno and a.sex=b.sex)
-
總結
select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id and u.age<'23';
-- 對於left join，on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄，on後面的只作為關聯條件。
select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id where u.age<'23';
-- where條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉
比較好的寫法
select u.*,d.* from (select * from userinfo where age<'23') u left join deptinfo d on u.dept=d.id;
如有紕漏，歡迎指正。
