数据库的高级查询五：表连接查询：内连接

这篇博客，多看下举的案例，多思考为什么。。。OK

目录

零：笛卡尔积

一：表连接：内连接简介

1.做表连接的时候，需要指定关联的条件。如果只是用JOIN来连接两站表，而没有ON指定连接条件，两张表连接的结果就是笛卡尔积了；

2.JOIN连接表，ON规定连接条件

二：表连接的分类之：内连接

1.内连接简介

2.内连接案例（核心，非常重要）

---

零：笛卡尔积

笛卡尔积介绍：（此图转载自SQL的多表查询(笛卡尔积原理)）；

（1）注意一下【2×2=4】，【4×2=8】；

（2）笛卡尔积：将两张表记录可以组合的所有可能，得到一张新的表；，

（3）笛卡尔积的语法：只是用JOIN来连接两站表，并没有连接条件；

SELECT *
FROM t_emp CROSS JOIN t_dept;

或者：有没有CROSS，效果一样

SELECT *
FROM t_emp JOIN t_dept;

 

（4）两张表中相同的字段，都会保留，如：

---

一：表连接：内连接简介

1.做表连接的时候，需要指定关联的条件。如果只是用JOIN来连接两站表，而没有ON指定连接条件，两张表连接的结果就是笛卡尔积了；

2.JOIN连接表，ON规定连接条件

（1）使用ON来规定连接的条件，这儿的条件是：emp表的deptno字段值=dept表的deptno字段值；

（2）在FROM子句中，给表起了一个别名；

（3）在SELECT子句中，ON子句中都使用了表的别名，来具体确定是哪张表；

（4）因为，有了ON后面的连接条件，符合条件的才会被保留下来，自然不会出现笛卡尔积那种情况了；

---

二：表连接的分类之：内连接

……………………………………………………

注：（20210406）经过实测，内连接可以不使用ON，话句话说，内连接后面可以不跟限定条件：

……………………………………………………

1.内连接简介

内连接的语法有多种变形的形式：（虽然下面几种内连接的写法不一样，但效果是一样的，而且执行的效率也都是一样的）

写法一：内连接最标准的写法：

写法二：ON子句中规定的是连接的条件，是用来筛选符合条件的记录的；那么自然可以将ON子句中的条件，写在WHERE子句中；

忽略了ON子句，而是使用WHERE，将连接条件写在WHERE子句中。

写法三：省略JOIN关键字，用逗号代替。。。私以为这种简写形式不咋地；

注：SELECT……FROM 表1，表2 ON 连接条件，这样是不行的！！！

2.内连接案例（核心，非常重要）

（1）查询每个员工的工号，姓名，部门名称，底薪，职位，工资等级：：这个例子，简单一点，直接连接，然后筛选符合条件的记录就行；

我的解决办法：

SELECT e.empno,ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno JOIN t_salgrade s ON ((e.sal>=s.losal) AND (e.sal<=s.hisal))

改进的一种写法：

SELECT e.empno,ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

这个例子的一个点：内连接的数据不一定必须有同名字段，只要字段之间符合逻辑关系就行；

（2）查找与SCOTT相同部门的员工都有谁：：这例子复杂点，需要先查询SCOTT的部门，然后获取这个部门的其他员工；；；但是这个例子不涉及聚合函数。

最容易想到的策略：利用子查询的方法：

SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT") AND ename!="SCOTT";

但是这种子查询的方法，虽然容易理解，非常符合一般的思考逻辑。但是数据库在执行子查询的时候，速度是非常的慢的。上面每执行一次WHERE语句，都要执行一次【SELECT deptno FROM t_emp WHERE ename="SCOTT"】，执行的次数直接平方了，特别耗时，能懂？？？

利用表连接的方法：

SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno 
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

要理解上面表连接的方法，多想想【从笛卡尔积中筛选出符合条件的记录】这句话，脑子里想一下表连接成笛卡尔积的过程和从笛卡尔积中筛选的过程就明白了。 

这个例子的一个点：相同数据表也可以是做表连接的。

（3）查询底薪超过公司平均底薪的员工信息：：这例子复杂点，需要先查询部门的平均底薪，然后获取这个部门底薪超过公司平均底薪的员工；；；但是这个例子涉及聚合函数。聚合函数不能放在WHERE或ON这种筛选的语句中哦！！！

这个问题，也是数据库的高级查询二：聚合函数这篇博客最后的遗留问题

最容易想到的策略，依旧是子查询：

SELECT ename,sal
FROM t_emp
WHERE sal>(SELECT AVG(sal) FROM t_emp);

这种策略，符合一般的思考逻辑，但就是复杂度太大，效率太低；

利用表连接的方法：

利用表连接的方法：

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t
ON e.sal>t.avg;

分析：【(SELECT AVG(sal) avg FROM t_emp) t】，把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。然后再从连接的笛卡尔积表中筛选出符合条件的记录；

（4）查询RESEARCH部门的人数，最高底薪，最低底薪，平均底薪，平均工龄：：：这个例子还好，启发：【同一个问题有多种解决方案，多多练习，更加深入后就能选择更优的解决方案】。

我的做法：

SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate))
FROM t_emp e JOIN (SELECT deptno dno FROM t_dept WHERE dname="RESEARCH") d
ON e.deptno=d.dno;

 标准的做法：

SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))-1
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

………………………………

注解：FLOOR()，ROUND()，CEIL()三个函数。

…………………………………………………………………………………………………………

（5）查询每种职业的最高工资，最低工资，平均工资，最高工资等级，最低工资等级：：：这个例子的教训：动脑子好吧，梳理清楚逻辑和过程！！！

我的做法：基本思路还是在笛卡尔积上做筛选。其中用到了【把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。】

SELECT e.maxx,e.minn,e.avgg,s.grade,e.job
FROM (SELECT job,MAX(sal) maxx,MIN(sal) minn,AVG(sal) avgg FROM t_emp GROUP BY job) e JOIN t_salgrade s
ON (s.losal<=e.maxx AND s.hisal>=e.maxx) OR (s.losal<=e.minn AND s.hisal>=e.minn);

我的做法是不对的，做的时候只想到：当【聚合函数，表连接都需要的时候】，【把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。】是一种不错的解决思路！！但是这个思路虽然可以解决很多具体需求，但并不是普遍使用的，有的情况，就不能用这种思路。

标准的做法：

SELECT e.job,MAX(e.sal+IFNULL(e.comm,0)),MIN(e.sal+IFNULL(e.comm,0)),AVG(e.sal+IFNULL(e.comm,0)),MAX(s.grade),MIN(s.grade)
FROM t_emp e JOIN t_salgrade s ON e.sal+IFNULL(e.comm,0) BETWEEN s.losal AND s.hisal
GROUP BY e.job

………………………………………………………………………………

（6）查询每个底薪超过部门底薪的员工信息：：：

我的做法：当【聚合函数，表连接都需要的时候】，【把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。】是一种不错的解决思路！！

SELECT e2.ename,e2.empno,e2.sal,e1.avv,e2.deptno
FROM (SELECT deptno,AVG(sal) avv FROM t_emp GROUP BY deptno) e1 JOIN t_emp e2
ON e2.sal>e1.avv AND e1.deptno=e2.deptno;

注意一下，e1中查询了deptno，因为e1和e2做笛卡尔积后，其中会有很多配对错误的记录，需要利用e1.deptno=e2.deptno筛选配对正确的记录。 这也是非常重要的一个点！！！即，先查询一个东西当成一个表和其他表做连接的时候，为了筛选连接后配对的数据，先查询的那个可能需要查询一些额外的、附带的信息；

标准做法：

SELECT e.sal,e.empno,e.ename,t.avv
FROM t_emp e JOIN (SELECT deptno,AVG(sal) AS avv FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avv;