hive表的连接、聚合、where查询、having查询区别、过滤、子查询的使用

先创建两个文本
a.txt
a,1
b,2
c,3
d,4

b.txt
a,xx
b,yy
d,zz
e,pp

一、基本表连接、创建表、插入数据

create table t_a(name string,numb int)
row format delimited
fields terminated by ',';

create table t_b(name string,nick string)
row format delimited
fields terminated by ',';

load data local inpath '/home/had/a.txt' into table t_a;
load data local inpath '/home/had/b.txt' into table t_b;

–各类join
–内连接

select a.*,b.*
from t_a a join t_b b;

–左外连接 left outer join 没有的值会被null填充

select a.*,b.*
from
t_a a left outer join t_b b
on a.name=b.name;

–右外连接 right outer join

select a.*,b.*
from
t_a a right outer join t_b b
on a.name=b.name;