Mysql三张表连接查询

三张表如何连接？

案例：找出每一个员工的部门名称以及工资等级

员工表：e表
mysql> select empno,ename,deptno,sal from emp;
+-------+--------+--------+---------+
| empno | ename  | deptno | sal     |
+-------+--------+--------+---------+
|  7369 | SMITH  |     20 |  800.00 |
|  7499 | ALLEN  |     30 | 1600.00 |
|  7521 | WARD   |     30 | 1250.00 |
|  7566 | JONES  |     20 | 2975.00 |
|  7654 | MARTIN |     30 | 1250.00 |
|  7698 | BLAKE  |     30 | 2850.00 |
|  7782 | CLARK  |     10 | 2450.00 |
|  7788 | SCOTT  |     20 | 3000.00 |
|  7839 | KING   |     10 | 5000.00 |
|  7844 | TURNER |     30 | 1500.00 |
|  7876 | ADAMS  |     20 | 1100.00 |
|  7900 | JAMES  |     30 |  950.00 |
|  7902 | FORD   |     20 | 3000.00 |
|  7934 | MILLER |     10 | 1300.00 |
+-------+--------+--------+---------+
14 rows in set (0.02 sec)

部门表：d表
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

工资等级：s表
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

注意，解释一下

...
    A
join
    B
join
    C
on
    ...

表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

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

执行sql

mysql> select
    ->     e.ename,d.dname,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;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| MILLER | ACCOUNTING |     2 |
| WARD   | SALES      |     2 |
| MARTIN | SALES      |     2 |
| ALLEN  | SALES      |     3 |
| TURNER | SALES      |     3 |
| CLARK  | ACCOUNTING |     4 |
| JONES  | RESEARCH   |     4 |
| SCOTT  | RESEARCH   |     4 |
| FORD   | RESEARCH   |     4 |
| BLAKE  | SALES      |     4 |
| KING   | ACCOUNTING |     5 |
+--------+------------+-------+
14 rows in set (0.00 sec)

案例：找出每一个员工的部门名称，工资等级、以及上级领导？

外连接的方式

select
    e.ename '员工',d.dname,e1.ename '领导'
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 e1
on
    e.mgr = e1.empno;    

sql 执行

mysql> select
    ->     e.ename '员工',d.dname,e1.ename '领导'
    -> 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 e1
    -> on
    ->     e.mgr = e1.empno;
+--------+------------+-------+
| 员工      | dname      | 领导    |
+--------+------------+-------+
| SMITH  | RESEARCH   | FORD  |
| ADAMS  | RESEARCH   | SCOTT |
| JAMES  | SALES      | BLAKE |
| MILLER | ACCOUNTING | CLARK |
| WARD   | SALES      | BLAKE |
| MARTIN | SALES      | BLAKE |
| ALLEN  | SALES      | BLAKE |
| TURNER | SALES      | BLAKE |
| CLARK  | ACCOUNTING | KING  |
| JONES  | RESEARCH   | KING  |
| SCOTT  | RESEARCH   | JONES |
| FORD   | RESEARCH   | JONES |
| BLAKE  | SALES      | KING  |
| KING   | ACCOUNTING | NULL  |
+--------+------------+-------+
14 rows in set (0.02 sec)