（四）sql多表连接查询join on的用法_join on多表的用法

（四）sql多表连接查询join on的用法总结

需要用到的表信息如下：
EMP职员表：

dept部门信息表和salgrade薪资等级表：

1. 多表连接
没有任何条件限制，即笛卡儿积，产生的记录数目为各表记录的乘积。
select * from table1，table2，table3…
select * from emp,dept

2. 等价连接
在所得的笛卡儿积中，筛选出有效的信息。在n张表之间，至少（n-1）个一个连接条件。例子如下：
select * from table1,table2 where table1.a=table2.a
SELECT * FROM emp as e ,deptl as d
WHERE e.DEPTNO=d.DEPTNO

3. 不等价连接
当某个值在某一范围中，需要求出此范围的级别时，可以应用不等价连接。
比如：显示员工姓名，工资，工资级别
SELECT e.ENAME,e.SAL,g.GRADE FROM emp e,salgrade g
WHERE e.SAL
BETWEEN g.LOW_SAL AND g.HIGH_SAL
注意：如果一步写不出来，可以分解开来。
首先，求出笛卡尔积，再从笛卡儿积中筛选条件添加到语句中。

4. 自然连接
自动的寻找2表中的（所有）同名且属性相同的列作为连接条件。使用natural join子句来完成。
例如：A表中有列a，b，c，d B表中有a，b，x，z
自然连接会将A.a=B.a and A.b=B.b 作为连接条件
select * from A natural join B (natural 不可以省略)。他们所得的结果中，同名且属性相同的字段只显示一个。

5. using子句的用法
using用于2张表的join查询，using子句可以指定用几个相同的名字和数据类型的列作为连接条件。
select * from table1 join table2 using(列名) 。
SELECT * FROM emp join dept using (deptno)

6. using 子句和等价连接的区别
using也是将2个表中属性列名相同的字段连接起来，不同的是等价连接是将所有的相同的都连接，而using（字段名）是将需要的字段连接起来（可以不是全部相同的）。

7. 比较下面的几个语句。

1 SELECT * FROM emp JOIN dept

2 SELECT * FROM emp NATURAL JOIN DEPT

3 SELECT * FROM emp JOIN dept USING (DEPTNO)

4 SELECT * FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO
语句1 是笛卡儿积，所得结果为2张表的记录数乘积。 脑部结果。
语句2和3，这里都是同一个结果如图，但是当emp表和dept表中有2个相同的列时候，他们结果就不一样了。这里就不演示了。
语句4 是等价连接。结果如图，它没有去掉重复的列。

8. 左外连接 left outer join … on …
左边的表是基本表，将左边的表的信息全部显示（不论是否可以有匹配，即为空也显示）。
SELECT * from emp e LEFT outer join dept d on e.DEPTNO=d.DEPTNO

9. 右外连接 right outer join … on …
右边的表是基本表，将右边的表的信息全部显示（不论是否可以有匹配，即为空也显示）。
SELECT * from emp e right outer join dept d on e.DEPTNO=d.DEPTNO

10. group by 分组
写在前面,sql关键词的执行顺序 ：
from>where>group by>having>select>order by
group by （字段）是将表中的字段按照需求进行分组。通常与分组函数进行联合使用。
一般来说，select语句中，除了5个分组函数，其余出现的列名必须在group by子句中。
group by 语句中的列名不一定要出现在select语句中。
如果条件需要用到分组函数，则条件需要写在having子句中。
分组条件一定要有意义。
查询出每个部门的最低最高工资
SELECT DEPTNO ,min(SAL),max(SAL) FROM emp GROUP BY DEPTNO
思考：查询出每个部门的最低最高工资，同时查出此人的姓名？
1 查出每个部门的最低工资
2 将emp表与其做连接
方法1
SELECT e.DEPTNO,e.ENAME,e.SAL FROM emp e JOIN (SELECT DEPTNO ,min(SAL) minsal FROM emp GROUP BY DEPTNO)a on (e.DEPTNO=a.DEPTNO and e.SAL=a.minsal)
方法2
SELECT * FROM emp WHERE (DEPTNO,SAL) in (SELECT DEPTNO,min(SAL) FROM emp GROUP BY DEPTNO) 2次匹配，第一次匹配deptno，第二次匹配sal。列的属性得与in（）里面的列属性相同

11. having子句
having是用于筛选信息的，匹配。
Having关键字的作用则是，为聚合结果指定条件。

12. 分组查询的嵌套
注意：当使用嵌套时，不能出现其他列名

13. 子查询
注意子查询的结果到底是1个值还是多个值，多个值时候可以用any或者in或者all。
子查询优先于外部查询。
单行子查询：子查询结果只有一个值。一般使用> = < 。
多行子查询：子查询结果有多个值。使用any，in not in，all。

14. 试题练习
一、查询出最低工资是2000的部门是那个？以及下面3句的区别？
① SELECT DEPTNO,min(SAL) FROM emp
②SELECT DEPTNO,min(SAL) FROM emp GROUP BY DEPTNO
③SELECT DEPTNO,min(SAL) FROM emp GROUP BY DEPTNO HAVING min(SAL)=1000
①是查询出最低工资的部门是哪一个。
②是查询出每个部门的最低工资。
③是查询出那个部门的最低工资是1000的。
二、查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
SELECT d.DEPTNO,d.DNAME,COUNT(d.DEPTNO)
FROM
emp e join dept d on d.DEPTNO=e.DEPTNO
GROUP BY d.DEPTNO,d.DNAME
HAVING
count(d.DEPTNO) >
(SELECT (SELECT COUNT(*) FROM emp) / (SELECT count(*) FROM dept ) )