hive> select * from emp; OK 7369 smith clerk 7902 1980-12-17 800.0 20.0 NULL 7499 allen salesman 7698 1981-2-20 1600.0 300.0 30 7521 ward salesman 7698 1981-2-22 1250.0 500.0 30 7566 jones manager 7839 1981-4-2 2975.0 20.0 NULL 7654 martin salesman 7698 1981-9-28 1250.0 1400.0 30 7698 blake manager 7839 1981-5-1 2850.0 30.0 NULL 7782 clark manager 7839 1981-6-9 2450.0 10.0 NULL 7788 scott analyst 7566 1987-4-19 3000.0 20.0 NULL 7839 king president NULL 5000.00 10 NULL NULL NULL 7844 turner salesman 7698 1981-9-8 NULL NULL NULL 7876 adams clerk 7788 1987-5-23 1100.0 20.0 NULL 7900 james clerk 7698 1981-12-3 950.0 30.0 NULL 7902 ford analyst 7566 1981-12-3 3000.0 20.0 NULL 7934 miller clerk 7782 1982-1-23 1300.0 10.0 NULL Time taken: 0.369 seconds, Fetched: 14 row(s) hive> select * from dept; OK 10 ccounting 1700 20 research 1800 30 sales 1900 40 operations 1700 Time 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 msec OK 7499 allen sales 7521 ward sales 7654 martin sales Time 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 msec OK NULL NULL ccounting NULL NULL research 7499 allen sales 7521 ward sales 7654 martin sales NULL NULL operations Time 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 msec OK 7369 smith NULL 7499 allen sales 7521 ward sales 7566 jones NULL 7654 martin sales 7698 blake NULL 7782 clark NULL 7788 scott NULL 7839 king NULL 7844 turner NULL 7876 adams NULL 7900 james NULL 7902 ford NULL 7934 miller NULL Time 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 msec OK 7934 miller NULL 7902 ford NULL 7900 james NULL 7876 adams NULL 7844 turner NULL 7839 king NULL 7788 scott NULL 7782 clark NULL 7698 blake NULL 7566 jones NULL 7369 smith NULL NULL NULL ccounting NULL NULL research 7521 ward sales 7654 martin sales 7499 allen sales NULL NULL operations Time taken: 232.865 seconds, Fetched: 17 row(s)
