目录:
- 数据
- 建表
- 操作
正文:
1、数据
a:
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 7 | y |
| 8 | u |
+-------+---------+--+
b:
+-------+---------+--+
| b.id | b.name |
+-------+---------+--+
| 2 | bb |
| 3 | cc |
| 7 | yy |
| 9 | pp |
+-------+---------+--+
2、建表
create table a(id int,name string)
row format delimited
fields terminated by ',';
create table b(id int,name string)
row format delimited
fields terminated by ',';
load data local inpath '/home/hadoop/a.txt' into table a;
load data local inpath '/home/hadoop/b.txt' into table b;
3、操作
inner join 操作
select * from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+
left join 操作
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
right join 操作
select * from a right join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
full outer join 操作
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
left semi 操作
select * from a left semi join b on a.id=b.id;
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+
select * from b left semi join a on a.id=b.id;
+-------+---------+--+
| b.id | b.name |
+-------+---------+--+
| 2 | bb |
| 3 | cc |
| 7 | yy |
+-------+---------+--+