Hive SQL join语句

Hive SQL join语句

hive> select * from emp;OK7369 smith clerk 7902 1980-12-17 800.0 20.0 NULL7499 allen salesman 7698 1981-2-20 1600.0 300.0 307521 ward salesman 7698 1981-2-22 1250.0 500.0 307566 jones manager 7839 1981-4-2 2975.0 20.0 NULL7654 martin salesman 7698 1981-9-28 1250.0 1400.0 307698 blake manager 7839 1981-5-1 2850.0 30.0 NULL7782 clark manager 7839 1981-6-9 2450.0 10.0 NULL7788 scott analyst 7566 1987-4-19 3000.0 20.0 NULL7839 king president NULL 5000.00 10 NULL NULL NULL7844 turner salesman 7698 1981-9-8 NULL NULL NULL7876 adams clerk 7788 1987-5-23 1100.0 20.0 NULL7900 james clerk 7698 1981-12-3 950.0 30.0 NULL7902 ford analyst 7566 1981-12-3 3000.0 20.0 NULL7934 miller clerk 7782 1982-1-23 1300.0 10.0 NULLTime taken: 0.369 seconds, Fetched: 14 row(s)hive> select * from dept;OK10 ccounting 170020 research 180030 sales 190040 operations 1700Time taken: 0.695 seconds, Fetched: 4 row(s)

内连接

只有进行连接的两张表的数据都存在连接条件相匹配时才会查询出来。

hive> select e.empno,e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;Total MapReduce CPU Time Spent: 17 seconds 420 msecOK7499 allen sales7521 ward sales7654 martin salesTime taken: 168.967 seconds, Fetched: 3 row(s)

右外连接

右外连接：join关键字右边的表所有的记录都会返回。

hive> select e.empno,e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;Total MapReduce CPU Time Spent: 2 seconds 160 msecOKNULL NULL ccountingNULL NULL research7499 allen sales7521 ward sales7654 martin salesNULL NULL operationsTime taken: 58.49 seconds, Fetched: 6 row(s)

左外连接

左外连接：join关键字左边的表所有的记录都会返回。

hive> select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;Total MapReduce CPU Time Spent: 2 seconds 800 msecOK7369 smith NULL7499 allen sales7521 ward sales7566 jones NULL7654 martin sales7698 blake NULL7782 clark NULL7788 scott NULL7839 king NULL7844 turner NULL7876 adams NULL7900 james NULL7902 ford NULL7934 miller NULLTime taken: 102.474 seconds, Fetched: 14 row(s)

全外连接

第一部分：两张有对应关联关系数据。 第二部分：左表中在右边表找不到匹配的数据。 第三部分：右表中在左边表找不到匹配的数据

hive>select e.empno,e.ename,d.dname from emp e full join dept d on e.deptno=d.deptno;Total MapReduce CPU Time Spent: 1 minutes 2 seconds 230 msecOK7934 miller NULL7902 ford NULL7900 james NULL7876 adams NULL7844 turner NULL7839 king NULL7788 scott NULL7782 clark NULL7698 blake NULL7566 jones NULL7369 smith NULLNULL NULL ccountingNULL NULL research7521 ward sales7654 martin sales7499 allen salesNULL NULL operationsTime taken: 232.865 seconds, Fetched: 17 row(s)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。