本节我们用一个简单的公司管理系统，有三张表 EMP（员工信息表）, DEPT （部门信息表）, SALGRADE （薪资等级表）来演示子查询、多表查询、自连接
目录
子查询
单列子查询
单行子查询
多行子查询 - in,all,any
多列子查询
在 from 子句中使用子查询
合并查询
多表查询
语法
笛卡尔积
综合案例
自连接
子查询
即在 select 中嵌套 select 查询，嵌套的 select 称为子查询
单列子查询
单行子查询
单行子查询即子查询的最终结果只有一行的子查询
案例：
显示与 SMITH 同一部门的所有员工
select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');
多行子查询 - in,all,any
多行子查询即子查询的最终结果有两行及以上的子查询
in 关键字
语法
select ... from table where coulmn in (select 子查询的结果，有两行及以上);作用：如果 coulmn 列的数据在子查询结果中，那么条件为真，否则为假
案例：
查询和 10 号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10号部门的雇员
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno != 10;all 关键字
语法
select ... from table where coulmn > all (select 子查询的结果，有两行及以上); select ... from table where coulmn < all (select 子查询的结果，有两行及以上);作用：如果 coulmn 列的数据比所有的子查询结果都要大(小)（即大(小)于子查询结果的最大(小)值），那么条件为真，否则为假
案例：
显示工资比 30 号部门的所有员工的工资都要高的员工的姓名、工资和部门
select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno = 30);any 关键字
语法
select ... from table where coulmn > any (select 子查询的结果，有两行及以上); select ... from table where coulmn < any (select 子查询的结果，有两行及以上);作用：只要 coulmn 列的数据 有 比子查询结果大(小)的，那么条件为真，否则为假
案例：
显示工资只要比 30 号部门的任意员工的工资高的员工的姓名、工资和部门号（包含 30 号部门的员工）
select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);
多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言 的，而多列子查询则是指查询返回多个列数据的子查询语句
案例：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from EMP where (deptno, job) = (select deptno, job from EMP where ename='SMITH') and ename != 'SMITH';' = ' 也可以换成 ‘ in ’，即把 (deptno, job) 看成一个整体，如果子查询有多列，则匹配所有与 (deptno, job) 相同的列。
在 from 子句中使用子查询
在 MySQL 中，一切皆表，包括 select 查询过程的临时表，我们可以利用这些临时表，与现有的表或其他临时表做笛卡尔积，再从结果做筛选，得到一些有用的信息。
案例：
显示每个高于自己部门平均工资的员工的姓名、部门、工资、部门平均工资
// 步骤1；先显示每个部门的平均工资 mysql> select DEPTNO,avg(sal) from EMP group by DEPTNO; +--------+-------------+ | DEPTNO | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec) // 步骤二：将上面的临时表与 EMP 表做笛卡尔积 mysql> select * from EMP,(select DEPTNO,avg(sal) mysql> from EMP group by DEPTNO) as tmp; +-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | avg(sal) | +-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | 2916.666667 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 10 | 2916.666667 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 10 | 2916.666667 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 10 | 2916.666667 | //...(部分) // 步骤三：过滤掉上面的临时表的无意义的行，即 EMP.DEPTNO != tmp.DEPTNO 的行 mysql> select * from EMP,(select DEPTNO,avg(sal) mysql> from EMP group by DEPTNO) as tmp where EMP.DEPTNO = tmp.DEPTNO; +-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | avg(sal) | +-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | 2175.000000 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | 1566.666667 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | 2916.666667 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | 1566.666667 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | 2175.000000 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | 1566.666667 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | 2916.666667 | +-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+ 14 rows in set (0.00 sec) // 步骤四：利用上面的临时表，筛选出高于自己部门平均工资的员工的姓名、部门、工资、部门平均工资 mysql> select ENAME,SAL,EMP.DEPTNO,avg mysql> from EMP,(select DEPTNO,avg(sal) as avg from EMP group by DEPTNO) as tmp mysql> where EMP.DEPTNO = tmp.DEPTNO and EMP.SAL > avg; +-------+---------+--------+-------------+ | ENAME | SAL | DEPTNO | avg | +-------+---------+--------+-------------+ | ALLEN | 1600.00 | 30 | 1566.666667 | | JONES | 2975.00 | 20 | 2175.000000 | | BLAKE | 2850.00 | 30 | 1566.666667 | | SCOTT | 3000.00 | 20 | 2175.000000 | | KING | 5000.00 | 10 | 2916.666667 | | FORD | 3000.00 | 20 | 2175.000000 | +-------+---------+--------+-------------+ 6 rows in set (0.00 sec)上面的最终结果的表还可以作为临时表，与其他表做笛卡尔积......
合并查询
合并多个 select 的执行结果，可以使用集合操作符 union，union all
语法：
select ... union select ...; select ... union all select ...;union，union all 操作符用于取得两个结果集的并集。当使用union时，会自动去掉结果集中的重复行。当使用union all时,不会去掉结果集中的重复行。
案例：
将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, sal, job from EMP where sal>2500 -> union select ename, sal, job from EMP where job='MANAGER'; +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+ mysql> select ename, sal, job from EMP where sal>2500 union all -> select ename, sal, job from EMP where job='MANAGER'; +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+多表查询
实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。
语法
select ... from table_name1,table_name2,...;笛卡尔积
案例：显示雇员名、雇员工资以及所在部门的名字。雇员名、雇员工资来自 EMP 表，部门的名字来自 DEPT 表。
mysql> show tables; +-----------------+ | Tables_in_scott | +-----------------+ | BONUS | | DEPT | | EMP | | SALGRADE | +-----------------+ 4 rows in set (0.00 sec) mysql> select * from EMP; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec) 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) mysql> select * from EMP,DEPT; +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC | +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 20 | RESEARCH | DALLAS | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 40 | OPERATIONS | BOSTON | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 20 | RESEARCH | DALLAS | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 40 | OPERATIONS | BOSTON | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 20 | RESEARCH | DALLAS | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 40 | OPERATIONS | BOSTON | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 20 | RESEARCH | DALLAS | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 40 | OPERATIONS | BOSTON | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 20 | RESEARCH | DALLAS | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 40 | OPERATIONS | BOSTON | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ 56 rows in set (0.00 sec)观察发现：同时查询两张表（EMP 和 DEPT）时，是把 EMP 的每一行与 DEPT的每一行都组合起来，然后显示。这种穷举组合的方式称为两张表的笛卡尔积。
但这种组合方式组合后的一些行是没有意义的，比如：
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC | +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |史密斯在表中出现了四次，而上表的第二行才是我们想要的，所以我们要对 EMP 和 DEPT 合成的一张表进行条件筛选：
mysql> select ENAME,SAL,DNAME from EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO; +--------+---------+------------+ | ENAME | SAL | DNAME | +--------+---------+------------+ | CLARK | 2450.00 | ACCOUNTING | | KING | 5000.00 | ACCOUNTING | | MILLER | 1300.00 | ACCOUNTING | | SMITH | 800.00 | RESEARCH | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | ADAMS | 1100.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | TURNER | 1500.00 | SALES | | JAMES | 950.00 | SALES | +--------+---------+------------+ 14 rows in set (0.00 sec)综合案例
显示部门号为10的部门名，员工名和工资
select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;显示各个员工的姓名，工资，及工资级别
select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;
自连接
对同一张表做笛卡尔积，叫做表的自连接
语法
select ... from table_name as 重命名1,table_name as 重命名2;案例：
显示员工FORD的上级领导的编号和姓名
mysql> select * from EMP; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)方法一：使用子查询
select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');方法二：使用多表查询（自连接）
select leader.empno,leader.ename from emp as leader, emp as worker where leader.empno = worker.mgr and worker.ename='FORD';
