mysql 和 hive 中几种关联（join/union） 的区别

mysql

两个表如下：

mysql> select * from t1 ;
+----+--------+
| id | Name
|
+----+--------+
|
2 | tim
|
|
3 | hannah |
|
4 | samuel |
|
1 | jacob
|
+----+--------+
mysql> select * from t2 ;
+----+--------+
| id | Name
|
+----+--------+
|
1 | hello
|
|
2 | jacob
|
|
3 | hi
|
|
4 | hannah |
+----+--------+

1.INNER JOIN

Inner join 产生的结果集，是t1和t2的交集。

mysql> SELECT * FROM t1
-> INNER JOIN t2
-> ON t1.name=t2.name;
+----+--------+----+--------+
| id | Name
| id | Name
|
+----+--------+----+--------+
|
1 | jacob
|
2 | jacob
|
|
3 | hannah |
4 | hannah |
+----+--------+----+--------+

JOIN

join和inner join 结果貌似一致。
join 直接将2表进行笛卡尔积。

mysql> SELECT *
-> FROM t1
-> JOIN t2;
+----+--------+----+--------+
| id | Name
| id | Name
|
+----+--------+----+--------+
|
1 | jacob
|
1 | hello
|
|
2 | tim
|
1 | hello
|
|
3 | hannah |
1 | hello
|
|
4 | samuel |
1 | hello
|
|
1 | jacob
|
2 | jacob
|
|
2 | tim
|
2 | jacob
|
|
3 | hannah |
2 | jacob
|
|
4 | samuel |
2 | jacob
|
|
1 | jacob
|
3 | hi
|
|
2 | tim
|
3 | hi
|
|
3 | hannah |
3 | hi
|
|
4 | samuel |
3 | hi
|
|
1 | jacob
|
4 | hannah |
|
2 | tim
|
4 | hannah |
|
3 | hannah |
4 | hannah |
|
4 | samuel |
4 | hannah |
+----+--------+----+--------+

2.FULL [OUTER] JOIN

实际上，mysql从来都不支持 full outer join，mysql根本就不识别outer关键字。
hive才支持

只有如下这一种情况，full join 可以执行，加上on条件，就会出现语法报错。
mysql> SELECT *
-> FROM t1
-> FULL JOIN t2;
+----+--------+----+--------+
| id | Name
| id | Name
|
+----+--------+----+--------+
|
1 | jacob
|
1 | hello
|
|
2 | tim
|
1 | hello
|
|
3 | hannah |
1 | hello
|
|
4 | samuel |
1 | hello
|
|
1 | jacob
|
2 | jacob
|
|
2 | tim
|
2 | jacob
|
|
3 | hannah |
2 | jacob
|
|
4 | samuel |
2 | jacob
|
|
1 | jacob
|
3 | hi
|
|
2 | tim
|
3 | hi
|
|
3 | hannah |
3 | hi
|
|
4 | samuel |
3 | hi
|
|
1 | jacob
|
4 | hannah |
|
2 | tim
|
4 | hannah |
|
3 | hannah |
4 | hannah |
|
4 | samuel |
4 | hannah |
+----+--------+----+--------+

3.LEFT [OUTER] JOIN

Left outer join 产生表 t1 的完全集，而 t2 表中匹配的则有值，没有匹配的则以null值取代。

mysql> SELECT *
-> FROM t1
-> LEFT JOIN t2
-> ON t1.name=t2.name;
+----+--------+------+--------+
| id | Name
| id
| Name
|
+----+--------+------+--------+
|
1 | jacob
|
2 | jacob
|
|
2 | tim
| NULL | NULL
|
|
3 | hannah |
4 | hannah |
|
4 | samuel | NULL | NULL
|
+----+--------+------+--------+

RIGHT [OUTER] JOIN

right join 和 left join 类似

mysql> SELECT *
-> FROM t1
-> RIGHT OUTER JOIN t2
-> ON t1.name=t2.name;
+------+--------+----+--------+
| id
| Name
| id | Name
|
+------+--------+----+--------+
| NULL | NULL
|
1 | hello
|
|
1 | jacob
|
2 | jacob
|
| NULL | NULL
|
3 | hi
|
|
3 | hannah |
4 | hannah |
+------+--------+----+--------+

UNION 与 UNION ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

mysql> SELECT name FROM t1
-> UNION
-> SELECT name FROM t2;
+--------+
| name
|
+--------+
| jacob
|
| tim
|
| hannah |
| samuel |
| hello
|
| hi
|
+--------+
本来有8个名字，但有2个重复，所以去除掉重复的名字，结果只有6个。
mysql> SELECT id,name FROM t1
-> UNION
-> SELECT id,name FROM t2;
+----+--------+
| id | name
|
+----+--------+
|
1 | jacob
|
|
2 | tim
|
|
3 | hannah |
|
4 | samuel |
|
1 | hello
|
|
2 | jacob
|
|
3 | hi
|
|
4 | hannah |
+----+--------+
id和name作为一个整体，没有重复的，所以结果显示8条。
mysql> SELECT name FROM t1
-> UNION all
-> SELECT name FROM t2;
+--------+
| name
|
+--------+
| jacob
|
| tim
|
| hannah |
| samuel |
| hello
|
| jacob
|
| hi
|
| hannah |
+--------+
union all，将结果无脑堆在一起。（相当于两个查询语句）

注意：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 只选取记录，而UNION ALL会列出所有记录。

hive

hive里面使用join时注意：

1、 只支持等值链接；多个条件，支持 and，不支持 or /大于/小于
例如：

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) 是正确的；
然而： SELECT a.* FROM a JOIN b ON (a.id>b.id)是错误的。

2、 可以 join 多于 2 个表
例如：

SELECT a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key2)

hive的join分成三类：

1、内连接
inner join
2、外连接
left outer join
right outer join
full outer join
3、半连接
left semi join

下面做实验：
2个表如下：

hive> select * from tablea;
OK
tablea.id
tablea.name
1
huangbo
2
xuzheng
4
wangbaoqiang
6
huangxiaoming
7
fengjie
10
liudehua
hive> select * from tableb;
OK
tableb.id
tableb.age
2
20
4
50
7
80
10
22
12
33
15
44

inner join（内连接）（把符合两边连接条件的数据查询出来）

hive> select * from tablea a inner join tableb b on a.id=b.id;
OK
a.id
a.name
b.id
b.age
2
xuzheng
2
20
4
wangbaoqiang
4
50
7
fengjie
7
80
10
liudehua
10
22

left join（左连接，等同于 left outer join）

• 1、以左表数据为匹配标准，左大右小
• 2、匹配不上的就是 null
• 3、返回的数据条数与左表相同

hive> select * from tablea a left join tableb b on a.id=b.id;
OK
a.id
a.name
b.id
b.age
1
huangbo
NULL
NULL
2
xuzheng
2
20
4
wangbaoqiang
4
50
6
huangxiaoming
NULL
NULL
7
fengjie
7
80
10
liudehua
10
22

right join（右连接，等同于 right outer join）

• 1、以右表数据为匹配标准，左小右大
• 2、匹配不上的就是 null
• 3、返回的数据条数与右表相同

hive> select * from tablea a right join tableb b on a.id=b.id;
OK
a.id
a.name
b.id
b.age
2
xuzheng
2
20
4
wangbaoqiang
4
50
7
fengjie
7
80
10
liudehua
10
22
NULL
NULL
12
33
NULL
NULL
15
44

left semi join（左半连接）

因为 hive 不支持 in/exists 操作（1.2.1 版本的 hive 支持 in 的操作），所以用该操作实现，并且是 in/exists 的高效实现。

hive> select * from tablea a left semi join tableb b on a.id=b.id;
OK
a.id
a.name
2
xuzheng
4
wangbaoqiang
7
fengjie
10
liudehua

可以修改为 in 的那种写法：

hive> select * from tablea where a.id in(select b.id from b);
OK
tablea.id
tablea.name
2
xuzheng
4
wangbaoqiang
7
fengjie
10
liudehua

虽然用in 也能实现，但不建议使用。一般用 left semi join 实现。

full outer join（完全外链接）

Full outer join 产生A和B的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。

hive> select * from tablea a full outer join tableb b on a.id=b.id;
OK
a.id
a.name
b.id
b.age
1
huangbo
NULL
NULL
2
xuzheng
2
20
4
wangbaoqiang
4
50
6
huangxiaoming
NULL
NULL
7
fengjie
7
80
10
liudehua
10
22
NULL
NULL
12
33
NULL
NULL
15
44

最后

以上就是无辜鸡翅为你收集整理的mysql 和 hive 中几种关联（join/union） 的区别的全部内容，希望文章能够帮你解决mysql 和 hive 中几种关联（join/union） 的区别所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。