hive查询where join_Hive连接查询join用法详解

先来个总的概括，Hive的join用法基本与SQL一致

建表语句不说了，直接看看两个表中的数据

表一：cl_student

hive (test)> select * from cl_student;

OK

1chenli21

2xuzeng22

3xiaodan23

4hua24

表2：cl_stu_sub

hive (test)> select * from cl_stu_sub;

OK

1chinese

2english

3science

5nature

1、内连接。inner join，即基于on语句，仅列出表1和表2符合连接条件的数据。

hive (test)> select a.*,b.* from cl_student a join cl_stu_sub b on a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

hive (test)> select a.*,b.* from cl_student a,cl_stu_sub b where a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

第一条语句与第二条SQL条语句的效果是一样的

2、左连接。左连接是显示左边的表的所有数据，如果有右边表与之对应，则显示；否则显示NULL

hive (test)> select a.*,b.* from cl_student a left outer join cl_stu_sub b on a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

4hua24NULLNULL

深入了解，比较两者的不同，在上述语句中增加了一个where条件或者是and的条件

on条件会产生一个临时表，where条件是对这个临时表进行过滤

hive (test)> select a.*,b.* from cl_student a left outer join cl_stu_sub b on a.id=b.id where b.name='chinese';

1chenli211chinese

hive (test)>select a.*,b.* from cl_student a left outer join cl_stu_sub b on a.id=b.id where a.name='chenli';

1chenli211chinese

标准查询关键字执行顺序为 from->where->group by->having->order by

on是先对表进行筛选后再关联的，left关联则on对右表才有效的，左表都是要选出来的

如果是要条件查询后才连接应该把查询件放置于ON后。如果是想再连接完毕后才筛选就应把条件放置于where后面，对主表的帅选要用where条件

hive (test)>select a.*,b.* from cl_student a left outer join cl_stu_sub b where a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

hive (test)>select a.*,b.* from cl_student a left outer join cl_stu_sub b on a.id=b.id and a.name='chenli';

1chenli211chinese

2xuzeng22NULLNULL

3xiaodan23NULLNULL

4hua24NULLNULL

hive (test)>select a.*,b.* from cl_student a left outer join cl_stu_sub b on a.id=b.id and b.name='chinese';

1chenli211chinese

2xuzeng22NULLNULL

3xiaodan23NULLNULL

4hua24NULLNULL

3、右连接.与左连接同理

hive (test)> select a.*,b.* from cl_student a right outer join cl_stu_sub b on a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

NULLNULLNULL5nature

4、全连接。相当于表1和表2的数据都显示，如果没有对应的数据，则显示NULL

hive (test)> select a.*,b.* from cl_student a full outer join cl_stu_sub b on a.id=b.id;

1chenli211chinese

2xuzeng222english

3xiaodan233science

4hua24NULLNULL

NULLNULLNULL5nature

5、左半开连接。left semi join，语法与左连接不一样，只能选择出左边表的数据，此数据符合on后面的条件。

semi join通常比inner join的效率更高

hive (test)>select a.* from cl_student a left semi join cl_stu_sub b on a.id=b.id;

1chenli21

2xuzeng22

3xiaodan23

hive (test)>select * from cl_student a left semi join cl_stu_sub b on a.id=b.id;

1chenli21

2xuzeng22

3xiaodan23

hive (test)>select a.*,b.* from cl_student a left semi join cl_stu_sub b on a.id=b.id;

FAILED: SemanticException [Error 10009]: Line 1:11 Invalid table alias 'b'

6、Hive不支持右半开连接

7、笛卡尔积join

笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数。

hive (test)>select a.*,b.* from cl_student a join cl_stu_sub b;

1chenli211chinese

1chenli212english

1chenli213science

1chenli215nature

2xuzeng221chinese

2xuzeng222english

2xuzeng223science

2xuzeng225nature

3xiaodan231chinese

3xiaodan232english

3xiaodan233science

3xiaodan235nature

4hua241chinese

4hua242english

4hua243science

4hua245nature