hive sql的笛卡儿积如何实现 sql查询笛卡尔积

连接查询

连接查询即跨越多张表查询数据。

表的连接方式：
（1）内连接：
			等值连接
			非等值连接
			自连接
（2）外连接：
			左外连接
			右外连接
全连接

（一）简单连接查询：笛卡尔积现象：

当两张表连接时没有任何条件限制会发生笛卡尔积现象,即两个不同表中的不同字段数据会进行一对一匹配，最终生成字段1×字段2数量的数据。
select 字段名,字段名 from 表名,表名;

为了避免笛卡尔积现象，连接时必须加条件：

select
	字段名,字段名
from
	表1，表2
where 
	表1.字段 = 表2.字段;

例如这两张表：

案例：查询每个员工对应的部门名称。这里能看到第一张表每个员工（ename）有个部门编号（deptno），第二张表每个部门编号（deptno）对应一个部门名称（dname）。

select 
	ename,dname
from 
	emp,dept
where
	emp.deptno = dept.deptno;

但是要注意的是这里任然是进行了14*4次的匹配，只不过总终会筛选出所想要的数据显示出来。而为了书写方便一般都要对表起别名，起名方式和查询方式如下:

select 
	e, d
from 
	emp e,dept d;//起别名
where
	e.deptno = d.deptno;

（二）内连接

（1）等值连接（条件为等量关系）

先举个例子：查询每个员工所在的部门名称，显示员工名（ename）和部门名（dname）

select 
	e.ename,d.dname
from 
	emp e,dept d
where
	e.deptno = d.deptno;

①inner… join … on …

select
	...
from
	表a
inner join
	表b
on
	连接条件

所以上面的查询也可以这样写：

select 
	e.ename,d.name
from 
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

②非等值连接

案例：对于这么两张表，找出每个员工的薪资等级（grade）、显示员工名（ename）、薪资（sal）、薪资等级（grade）。

这里能注意到两张表已经没有一样的相同字段来进行等值连接了，

select 
	e.ename,e.sal,s.grade
from
	emp e
inner join 
	salgrade s
on 
	e.sal between s.losal and s.hisal; //在这个工资内属于这一等级 连接条件没有等量关系

③自连接

案例：查询员工的上级领导，要求显示员工名（ename）、对应领导名（mgr内容编号所对应的ename。例入这里smith的领导编号为7902，那么其领导名字为ford）。

技巧：一张表看做两张表,一张表起两个别名。

select
	e.ename,s.ename
from
	emp e
inner join
	emp s
on
	e.mgr = s.empno;

（二）外连接

在上面简单查询和内连接中都能够将匹配省条件的数据查询出来。
例如上面：这是查询每个员工所在的部门名称，同时又因为两张表都有deptno可以进行匹配所以，可以写e.deptno = d.deptno的查询条件。

select 
	e, d
from 
	emp e,dept d;//起别名
where
	e.deptno = d.deptno;

但是如果我想不仅查询有员工的部门名称，没有员工的部门我也要显示出来。这里能看到上表中没有部门号为40的员工，所以我要显示的话，不仅要进行匹配，还要把第二张表全部部门显示出来，那么这里就需要外连接。

①right和left关键字

right关键字会将右边这张表看做主表，那么会将主表的全部内容查询出来，顺带查询一下关联查询左边的表。所以在外连接中是有表的主次关系，在内连接中两张表没有主次关系，是平等的。
右外连接（右边是主表用right）：

select
	e.ename,d.dname
from 
	emp e 
right join 
	dept d
on 
	e.deptno = e.deptno;

左外连接（左边为主标left）：

select
	e.enmae,d.name
from
	dept d 
left outer join 
	emp e
on
	e.deptno = e.deptno;

案例：查询每个员工的上级领导，要求显示所有员工的名字（ename）和领导名（mgr->ename）。

很明显这里主在显示所有员工名字，再顺便查询：

select
	e.ename,s.ename
from
	emp e
left join 
	emp s
on
	s.mgr = e.empno;

（三）多表连接

语法：join… on… 连用

select 
  ...
from
	表a
join 
	表b
on
	表a和表b的连接条件
join
	表c
on
	表a和表c的连接条件
...

当连接很多表时join on连用是基本的语法，当然内连接和外连接的混合也可以使用。

案例：

select 
	e.ename,d.ename,e.sal,s.grade
from
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal;

那么如果还要显示员工的上级领导名呢？

select 
	e.ename,d.ename,e.sal,s.grade
from
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal;
left join
	emp l
on
	e.mgr = l.empno;

（四）子查询

子查询即对select的嵌套：

select 
	..(selrct)
from
	..(select)
where
	..(select)

（1）where中的子查询

案例：找出比最低工资高的员工姓名和编号：

select 
	ename,empno
from
	emp
where
	select sal>(select min(sal) from emp);
	//sal>min(sal);  //这里是错误的注意多行处理函数只能在select中出现

（2）from中的子查询
from中的子查询，可以将其查询结果当做一张临时表。
案例：
找出每个岗位的平均薪资的工资等级。
①首先第一步：按照工作岗位分组查询每个岗位的平均工资。

select
	job,avg(sal)
from 
	emp
group by
	job;

②把上面的查询结果当做一张表来进行多表查询其平均工资等级。

select
	s.grade,t.*
from 
	(select
		job,avg(sal) as avgsal //这里必须给avg(sal)起别名
	from 
			emp
	group by
			job;)  t
join 
	salgrade
on
	t.avgsal between s.losal and hisal;
	//不起别名，avg带上括号会被认为是函数，但是函数又必须在select后面。
	//t.avg(sal) between s.losal and hisal;

（五）union合并查询结果

案例：查询工资等于800和1k的员工名。

select
	ename
from
	emp
where 
	sal=800 or sal = 1000;

那么用union可以这样写：

select ename from emp where sal=800 
union
select ename from emp where sal = 1000;

union相对于or效率更高，对于表连接来说，因为笛卡尔积的影响，匹配次数很多，而union把乘法变成加法，union可以减少匹配次数。但是注意列结果集合并时列和列数据必须相同。

（六）limit 取有限数据

limit会将查询集的一部分取出，经常用于分页查询中。在mysql当中limit在order by之后执行。

案例：对于这样一张表，按照降序查询前五个id的num

select 
	num,id
from 
	citymessage;
order by 
	desc
limit
	5;//一个数字：取前五
	//0,5; //两个数字是：0表示起始位置，5表示所取数据的长度

这里需要注意，limit num1,num2;这里取值和数组类似。

num1从0开始，num2取得时候前后都要算，例如我要取[5,9]之间的数据，那么num1从0开始算第五条数据应该是4，而5,6,7,8,9刚好五个数字，所以应该是：limit 4,5

（七）分页

案例：

这里其实能看到一个规律就是limit num1,num2，其中num1=（页码数-1）*每页记录数，num2=每页记录数，即：num1 = （页码数-1）*num2。