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;