mysql连接查询的改写_MySQL之连接查询

连接查询

在实际开发中，数据往往是同时存储在多张表中，这些表与表之间存在着关系，我们在检索数据的时候往往需要多张表联合起来检索，这种多表联合检索被称为链接查询。

笛卡尔乘积

显示每个员工信息，并显示所属部门名称：

select e.ename, d.dname from emp e, dept d;

上面SQL语句的结果数量是emp和dept两张表中数量的乘积，像这种多表进行连接查询时没有任何条件，最终的结果是多表结果数量乘积的现象被称为笛卡尔积。

正确的写法：

SQL92写法：

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

SQL99写法(建议使用)：

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

说明：在进行多表查询时，通常会给表取别名，这时可以通过别名来判断出字段属于哪个表，有较强的可读性。

连接的分类

先看下图，通过下图可以更好的理解不同连接之间的区别。

内连接

内连接查询出的数据是两张表的交集，即上图中C所表示的部分。

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

其中inner可以省略：

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

左外连接

左外链接如上图中A部分+C部分的内容，即包含左边表的全部行(不管右边的表中是否存在与它匹配的行)，和右边表中全部匹配的行。

右外链接

右外链接如上图中B部分+C部分的内容，即包含右边表的全部行(不管左边的表中是否存在与它匹配的行)，和左边表中全部匹配的行。

全外连接

MySQL中不支持

案例：

查询每个员工所在的部门名称，显示用工姓名和对应的部门名称：

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

找出员工所对应的工资等级，显示员工姓名、工资、工资等级：

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

查询员工的名称和员工对应的领导名称：

select a.ename empname ,b.ename leadername from emp a join emp b on a.mgr = b.empno;

说明：上面的写法是emp表自己跟自己做连接查询，这种写法叫做自连接。

找出每一个员工对应的部门名称，要求部门名称全部显示：

使用左连接：

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

其中outer可以省略：

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

使用右连接：

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

其中outer可以省略：

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

注意：右外连接都可改写成左外连接，左外连接都可改写成右外连接。

找出每一个员工对应的领导名，要求显示所有员工：

select a.ename empname ,b.ename leadername from emp a left join emp b on a.mgr = b.empno;

说明：查询结果中的king的领导是null，使用左连接或右连接才可以将其查询出来。

找出每一个员工对应的部门名称，以及该员工对应的工资等级，要求显示员工姓名、部门名称、工资等级:

SELECT

e.ename, d.dname, 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;