MySQL之复合查询

work_hard423

已于 2023-12-30 14:05:29 修改

阅读量1.2k

点赞数 25

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-12-28 13:33:10 首次发布

本文链接：https://blog.csdn.net/work_hard423/article/details/135266589

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

单表查询回顾

在讲解多表查询前，我们先回顾一下单表查询，这是因为多表查询本质上依然是单表查询（其原因在下文中讲解多表查询时再说明），只要掌握了单表查询，那么想掌握多表查询是非常简单的。

在<<MySQL之对表内容的增删查改>>一文中我们详细讲解过单表查询（注意该篇文章中的内容全是单表查询），为了回顾单表查询，咱们再把该篇文章中的emp员工表、dept部门表、salgrade工资等级表以及表中的数据全部照搬过来，至于如何照搬，咱们只需要通过【把下面的SQL语句拷贝到MySQL中执行一遍】即可做到。

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
USE `scott`;
 
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
 
 
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
 
 
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);
 
 
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
 
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

如下图所示，执行完上面的SQL语句后，就有数据库scott和三张表、以及各个表中的数据了。

准备工作完毕后，咱们进入正题。

查询工资高于500或岗位为MANAGER的员工，同时要求员工姓名的首字母为大写的J，如下：

如下图所示，因为工资（即属性sal）、岗位（即属性job）、员工姓名（即属性ename）都存在于emp表中，所以我们选择select from emp查询emp表。然后要在where子句中指明筛选条件为工资高于500或岗位为MANAGER，并且通过模糊匹配指明筛选条件为员工姓名的首字母要是大写的J。

查询员工的姓名、部门号以及工资，按部门号升序并且员工工资降序的方式进行显示，如下：

如下图所示，因为员工姓名（即属性ename）、部门号（即属性deptno）、工资（即属性sal）都存在于emp表中，所以我们选择select from emp查询emp表。然后要在select的属性列表中指明要查询的属性为姓名、部门号和工资，要在order by子句中依次指明按部门号排升序和按员工工资排降序，即不同部门的员工按照部门号排升序，而同一部门的员工按员工工资排降序。

查询员工信息，按年薪降序显示，如下：

先说一下，【年薪=12个月的月薪+最后的年终奖】，而因为员工姓名（即属性ename）、月薪（即属性sal）、年终奖（即属性comm）都存在于emp表中，所以我们选择select from emp查询emp表。

然后注意，在<<MySQL之对表内容的增删查改>>一文中讲解聚合函数时证明过，NULL不参与计算和比较，任何值强行和NULL相加减乘除，都会得到NULL，而下图中有些员工的年终奖就是NULL，所以这时计算该员工的年薪时就不应该让该员工的12个月的月薪加上年终奖NULL，以避免计算出的年薪为NULL，而应该让12个月的月薪加上0，如何做到这一点呢？

答案：ifnull(val1，val2)函数具有的特性是，把表示年终奖的comm传给参数val1、把0传给参数val2后，未来如果comm的值是NULL，则该函数就会返回0，如果comm的值不是NULL，则该函数才会返回comm的值，所以我们就能根据ifnull函数的特性、通过表达式【年薪=12个月的月薪+ifnull（comm，0）】来正确地计算年薪了。

有了计算年薪的方案后，剩下的步骤就简单了，如下图所示，只需在select的属性列表中指明要查询的属性为姓名和年薪，然后在order by子句中指明按年薪进行降序排序即可。

查询工资最高的员工的姓名和岗位，如下：

因为工资（即属性sal）、员工姓名（即属性ename）、岗位（即属性job）都存在于emp表中，所以我们选择select from emp查询emp表。

方式一：因为我们不知道表中的最高工资是多少，所以在查询工资最高的员工的姓名和岗位前，我们要先通过聚合函数max计算出表中的最高工资是多少，然后再通过where子句和该最高工资筛选出该员工的姓名和岗位，演示如下。

方式二：我们也可以使用子查询（关于子查询，会在下文中详细说明），将表示第一次查询的SQL语句用括号括起来，作为最高工资直接在表示第二次查询的SQL语句中使用。演示如下：

查询工资高于平均工资的员工的姓名和工资，如下：

因为工资（即属性sal）、员工姓名（即属性ename）都存在于emp表中，所以我们选择select from emp查询emp表。

方式一：因为我们不知道表中的平均工资是多少，所以在查询工资高于平均工资的员工的姓名和工资前，我们要先通过聚合函数avg计算出表中的平均工资是多少，然后再通过where子句和该平均工资筛选出该员工的姓名和工资，演示如下。

方式二：我们也可以使用子查询（关于子查询，会在下文中详细说明），将表示第一次查询的SQL语句用括号括起来，作为平均工资直接在表示第二次查询的SQL语句中使用。演示如下：

查询每个部门的平均工资和最高工资，如下：

如下图所示，因为工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。然后在group by子句中指明按照部门号进行分组，并在select语句中使用聚合函数avg和max分别查询每个部门的平均工资和最高工资即可。

查询平均工资低于2000的部门号和它的平均工资，如下：

如下图所示，因为工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。然后在group by子句中指明按照部门号进行分组，在select语句中使用聚合函数avg查询每个部门的平均工资，并在having子句中指明筛选条件为平均工资小于2000即可得到最终结果。

查询每种岗位的雇员总数和平均工资，如下：

如下图所示，因为岗位（即属性job）、工资（即属性sal）都存在于emp表中，所以我们选择select from emp查询emp表。然后在group by子句中指明按照岗位进行分组，并在select语句中使用聚合函数count和avg分别查询每种岗位的雇员总数和平均工资即可。

多表查询

如下图所示，有时为了节省MySQL的空间资源，我们会把本可以用一张表存储的数据通过多张表存储起来，然后再将多张表通过某个在每张表中都存在的属性（如下图的class_id属性就在每张表中都存在）关联起来，这是我们曾经在讲解外键约束时说过的内容。

而当数据被分散在多张表中存储时，有时候会在MySQL中发生这样的情景：现在有一个需求，就是需要select语句将表1中的某些属性列和表2中的某些属性列在一张表中显示出来，那么很显然，此时不论是靠【select from 表1】还是靠【select from 表2】都无法解决问题，毕竟表1无从知晓表2中的属性，表2也无从知晓表1中的属性。

那么该怎么办呢？此时就需要通过多表查询才能做到这一点。咱们先介绍一下多表查询，如下。

多表查询的SQL语法就是将多张表的表名依次放到from子句后，用逗号隔开，比如【select from 表1，表2，... 】。这时MySQL将会对指定的这多张表取笛卡尔积作为多表查询的初始数据源（如下图上半部分画红线的地方所示，笛卡尔积就是，先将emp表中的第一条数据依次和dept表中的每一条数据进行拼接，然后再将emp表中的第二条数据依次和dept表中的每一条数据进行拼接，以此类推直至emp表中的每一条数据都完成了和dept表进行拼接。如下图的下半部分所示，这张大表就是对emp表和dept表取笛卡尔积得到的结果，也就是说在当前情景下，这张大表就作为多表查询的初始数据源）。
然后注意，emp表和dept表取笛卡尔积得到的大表中，并不是每一条数据都是有意义的，比如说如上图下半部分的红框处所示，在emp表中，该条数据的属性deptno的值是20，但在dept表中，该条数据的属性deptno的值是10，那这就不匹配、这条数据就没有意义，这是因为从语义上说，明明该员工是属于20号部门，但却在该员工的信息中拼接了10号部门的所有信息。那怎样的一条数据才有意义呢？实际上我们能很轻松地理解到，一个员工的信息只有和自己所属的部门的信息进行组合才是有意义的。
所以综上所述，我们是需要对多张表的笛卡尔积做数据过滤的。如何过滤呢？答案：如果需要做多表查询，则多张表通常都是有关联的（如果在多张表之间没有关联的情况下还做多表查询，则此时虽然也可以做多表查询，但此时多表查询就没有什么意义，只会导致很多重复的数据冗余在一起），多张表会通过某个在每张表中都存在的属性被关联起来。而哪个属性将多张表关联起来，我们就要通过哪个属性做数据过滤。比如说在上图的情况中，emp表和dept表中都有属性deptno，并且也是deptno属性将emp表和dept表关联起来，所以我们过滤的方式就是判断笛卡尔积中的属于emp表的deptno属性值和笛卡尔积中的属于dept表的deptno属性值是否相等，如果相等，则说明该条数据有意义，如果不相等，则说明该条数据没有意义、需要将该条数据过滤掉，演示如下图所示，下图中的表就是笛卡尔积进行过滤后的结果，这时表中的每一条数据才都有意义（注意因为笛卡尔积中有两个deptno属性，所以我们要通过emp.deptno和dept.deptno这样的方式将二者区分。说一下，如果一个属性同时存在于用于取笛卡尔积的多张表中，则这时在笛卡尔积里选中该属性时需要通过表名.属性名这样的方式进行指明，当然如果一个属性不同时存在于用于取笛卡尔积的多张表中，而是只存在于一个表中，那么在笛卡尔积里选中该属性时就无需通过这样的方式了）。

如上图所示，我们可以看到，通过多表查询这样的方式，我们就可以把表1和表2拼接在一起形成一张更大的表，所以这样一来，通过select语句将表1中的某些属性列和表2中的某些属性列放在一张表中显示出来的需求也就很好满足了，这就是上面提出的问题【那么该怎么办呢？】的答案。

（结合下图思考）走到这里，我们就能初步理解多表查询的应用场景了，即如果要同时显示属性A和属性B，但属性A和属性B被分散在表1和表2中，那就可以通过多表查询将表1和表2拼接在一起形成笛卡尔积，这时在笛卡尔积中就同时有属性A和属性B了，这时只需对笛卡尔积做数据清洗以清洗掉无意义的数据，然后就可以按需进行同时显示属性A和属性B了。

同时注意，在本篇文章的第一段中说过【因为多表查询本质上依然是单表查询】这样一句话，走到这里我们就能深刻理解到，就是因为多表查询本质上只是将多张小表合并成一张大表，然后在这张大表中做查询，所以才说多表查询本质上依然是单表查询。

走到这里，对多表查询的基本介绍就进行完毕了，接下来咱们做一些多表查询的实操，如下。

显示部门号为10的部门名、员工名和员工工资，如下：

因为员工姓名（即属性ename）、工资（即属性sal）只存在于emp表中，部门名（即属性dname）只存在于dept表中，所以为了能在一张表中同时显示部门名、员工名、员工工资，我们要【select from emp,dept】多表查询emp和dept表以将这两张表拼接起来形成笛卡尔积，这样一来如下图1黄框处所示，在笛卡尔积中就同时有部门名、员工名、员工工资这三个属性了。

这时我们只需先在笛卡尔积中筛选出有意义的数据（像下图1红框处这样，一个员工属于20号部门，则给该员工拼接上20号部门的部门名信息才有意义，如果此时给该员工拼接上10或30号部门的部门名信息，则就没有意义）然后再指明要查询部门号为10的数据即可成功解题了。如下图2就是用于解题的SQL语句。

图1如下。
图2如下。

显示各个员工的姓名、工资和工资等级，如下：

因为员工姓名（即属性ename）、工资（即属性sal）只存在于emp表中，工资等级（即属性grade）只存在于salgrade表中，所以为了能在一张表中同时显示员工姓名、工资、工资等级，我们要【select from emp,salgrade】多表查询emp和salgrade表以将这两张表拼接起来形成笛卡尔积，这样一来如下图黄框处所示，在笛卡尔积中就同时有员工姓名、工资、工资等级这三个属性了。

然后要知道的是，如下图所示（下图的上半部分是取两张表笛卡尔积的过程，下图的下半部分是两张表的笛卡尔积），在员工表和工资等级表的笛卡尔积中，是将员工表中的每一个员工的信息都和工资等级表中的所有工资等级信息进行了组合，而实际上一个员工的信息只有和自己工资对应的工资等级信息进行组合才是有意义的，如果一个员工的工资不属于某个工资等级，但却将该工资等级的所有信息拼接在了员工信息里，那这条数据就没有意义（比如下图下半部分红框处的数据就没有意义，因为明明这个员工的工资是800，不在1201到1400这个区间中，却将这个区间代表的工资等级的所有信息拼接在了该员工的信息中），因此需要拿着员工的工资根据工资等级表中的各个工资等级的最低工资和最高工资判断一个员工的工资是否属于该工资等级，进而筛选出有意义的数据。

结合上面的思路，我们可知只需要在select from的时候多表查询emp表和salgrade表，并在where子句中指明筛选条件为员工的工资sal在losal和hisal之间（即如果sal在区间之内，则该条数据有意义，如果不在区间之内，则该条数据无意义，将其过滤掉），即可查询出最终结果，如下。

方式1如下。
方式2如下。

自连接

注意，我们不仅可以多表查询不同的多张表（即取不同表的笛卡尔积），还可以多表查询相同的多张表（即对同一张表取笛卡尔积），而像这样多表查询相同的多张表的情况就叫做自连接。

那自连接有什么用呢？咱们直接通过实操来说明其作用，如下。

显示员工FORD的上级领导的编号和姓名，如下：

因为员工姓名（即属性ename）、员工的编号（即属性empno）都只存在于emp表中，所以我们选择select from emp查询emp表。

然后注意，因为员工FORD的上级领导本质也是一个员工，所以理论上领导的员工姓名（即属性ename）和员工编号（即属性empno）也一定存在于emp表中，如下图1所示，可以发现的确如此，并且员工FORD的信息和他的领导的信息位于不同的行上。

这时我们就可以通过将这张表自连接形成一个更大的表，以让这个更大的表中存在一条【由员工FORD的所有信息和他的领导的所有信息】组成的数据（注意，由于自连接是对同一张表取笛卡尔积，因此在进行自连接时至少需要给该表取一个别名，否则未来用户在选中一个属性时，MySQL会分不清该属性属于哪张表），如下图2的红框处所示，可以发现的确有这样的一条数据。

然后剩下的工作就简单了，我们只需在这个更大的表中通过where子句将该条数据筛选出来（如何筛选呢？先让where子句把ename等于FORD的数据晒出来，然后加上and关键字把【员工的领导编号mgr等于领导的员工号empno的数据】筛选出来即可，注意这一步是在过滤掉所有没有意义的数据），并按照题目的要求进行显示即可完成题目的要求，如下图3所示。

图1如下。
图2如下。
图3如下。

说一下，除了上面的方法外，咱们还可以通过子查询完成该题目，比如说先对员工表emp进行查询得到FORD的领导的编号，然后再根据领导的编号对员工表进行查询得到FORD领导的姓名。如下。

所以走到这里我们就能明白自连接的作用，即：如果在一张表中，某条数据A的某些属性和另一条数据B有关系（比如在上面的情景中，就是一个员工A的属性mgr和另一个员工B有关系），并且现在又有显示数据B的信息的需求，那么就可以通过自连接将表中通过该属性进行关联的两条数据组合起来形成一条数据，然后再将这条数据筛选出来并按需进行显示（再次强调一下，想要通过自连接的方式显示数据B的信息，则数据A必须有某些属性和数据B有关系，否则后序在筛选数据时就没有依据、自连接也就没有意义）。

子查询

先说一下，子查询可分为单行单列子查询、单行多列子查询、多行单列子查询、多行多列子查询、以及在from子句中使用的子查询。

单行单列子查询

单行单列子查询就是只返回一条数据的一个属性值的子查询。

显示SMITH同一部门的员工，如下：

如下图所示，因为部门编号（即属性deptno）、员工姓名（即属性ename）都存在于emp表中，所以我们选择select from emp查询emp表。然后在子查询中查询SMITH所在的部门号，并在where子句中指明筛选条件为员工部门号等于子查询返回的部门号即可。

说一下，下图中子查询能够作为where子句的筛选条件，其就是因为，该子查询是单行单列子查询，而单行单列子查询本质只是返回了一条数据的一个属性值（如下图红框处所示），也就是一个值（注意数量为1），既然像【where deptno=30】这样让筛选条件是等于一个常数值是可行的，那么像【where deptno=(select deptno from emp where ename='SMITH')】这样这样让筛选条件是等于一个属性值也没有什么不合理的了。
如何判断出该子查询是单行单列子查询的呢？说一下，拿下图红框处举例，因为select时我们只指明了要一个属性deptno，所以当前子查询一定是单列查询；又因为下图红框中的查询结果只有一行，所以当前子查询就是单行查询。最后一结合就是单行单列子查询了。

多行单列子查询

多行单列子查询就是返回多条数据的一个属性值的子查询。接下来咱们结合实际案例来介绍多行单列子查询，如下。

（in关键字）题目：显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号，但是不包含10号部门的员工，如下：

如下图所示，因为员工姓名（即属性ename）、岗位（即属性job）、工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。

然后因为多行单列子查询本质是返回了多条数据的一个属性值（如下图红框处所示），也就是多个值，所以在多行单列子查询中查询出10号部门的所有岗位有哪些后，在where子句中指明筛选条件时需要加上in关键字，以判断员工的工作岗位是否是子查询得到的若干岗位中的一个，如果不是则将该员工的信息过滤掉，如果是则保留。由于题目要求筛选出来的员工不包含10号部门的，因此还需要在where子句中指明筛选条件为部门号不等于10。

如何判断子查询是否是多行单列子查询呢？说一下，拿下图红框处举例，因为select时我们只指明了要一个属性job，所以当前子查询一定是单列查询；又因为下图红框中的查询结果有多行，所以当前子查询就是多行查询。最后一结合就是多行单列子查询了。

（all关键字）题目：显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号，如下：

如下图所示，因为员工姓名（即属性ename）、工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。

然后因为多行单列子查询本质是返回了多条数据的一个属性值（如下图红框处所示），也就是多个值，所以在多行单列子查询中查询出30号部门的所有员工的工资后，在where子句中指明筛选条件时需要加上all关键字，以判断员工的工资是否高于子查询得到的所有工资，如果不是则将该员工的信息过滤掉，如果是则保留。

如何判断子查询是否是多行单列子查询呢？说一下，拿下图红框处举例，因为select时我们只指明了要一个属性sal，所以当前子查询一定是单列查询；又因为下图红框中的查询结果有多行，所以当前子查询就是多行查询。最后一结合就是多行单列子查询了。

除了上面的方法外，实际这道题也等价于找出工资高于30号部门的最高工资的员工，因此也可以使用单行单列子查询得到30号部门的最高工资，然后判断员工的工资是否高于子查询得到的最高工资即可。如下：

（any关键字）题目：显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号，包含30号部门的员工，如下：

如下图所示，因为员工姓名（即属性ename）、工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。

然后因为多行单列子查询本质是返回了多条数据的一个属性值（如下图红框处所示），也就是多个值，所以在多行单列子查询中查询出30号部门的所有员工的工资后，在where子句中指明筛选条件时需要加上any关键字，以判断员工的工资是否高于子查询得到的多个工资中的任意一个，如果不是则将该员工的信息过滤掉，如果是则保留。

如何判断子查询是否是多行单列子查询呢？说一下，拿下图红框处举例，因为select时我们只指明了要一个属性sal，所以当前子查询一定是单列查询；又因为下图红框中的查询结果有多行，所以当前子查询就是多行查询。最后一结合就是多行单列子查询了。

除了上面的方法外，实际这道题也等价于找出工资高于30号部门的最低工资的员工，因此也可以使用单行单列子查询得到30号部门的最低工资，然后判断员工的工资是否高于子查询得到的最低工资即可。如下：

最后说一下，为什么单行单列子查询不需要使用in、all、any关键字，而多行单列子查询需要呢？答案很简单，因为前者只会查到一条数据的属性值，而后者会查到多条数据的属性值。换言之，往后我们判断子查询是否需要使用这3个关键字的方式就是看子查询查出的数据是否有多条，如果是，则需要使用，如果不是，则不需要使用。注意，不需要使用不代表不可以使用，要知道即使子查询查出的数据只有一条，我们也是可以使用in、all、any关键字的，从语法角度上说这是没有问题的。

单行多列子查询

单行多列子查询就是返回一条数据的多个属性值的子查询。接下来咱们结合实际案例来介绍单行多列子查询，如下。

题目：显示和SMITH的部门和岗位完全相同的员工，不包含SMITH本人，如下：

因为员工姓名（即属性ename）、岗位（即属性job）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。

如下图所示，先查询SMITH所在部门的部门号和他的岗位，然后再将这个查询作为子查询，并在where子句中指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位，并且员工的姓名不为SMITH即可。

注意，单行多列子查询得到的结果是一条数据的多个属性值，而在比较多个属性值时就需要像下图那样将待比较的多个属性用圆括号括起来。
如何判断子查询是否是单行多列子查询呢？说一下，拿下图红框处举例，因为select时我们指明了要属性job和属性deptno，所以当前子查询一定是多列查询；又因为下图红框中的查询结果只有一行，所以当前子查询就是单行查询。最后一结合就是单行多列子查询了。

多行多列子查询

多行多列子查询就是返回多条数据的多个属性值的子查询。理解了上文的多行单列子查询和单行多列子查询的特点和性质后，再理解多行多列子查询的特点和性质就很简单了，即多行多列子查询的特点和性质就是【多行单列子查询和单行多列子查询】的特点和性质的总合版，也就是说，在和多行多列子查询查出的结果进行比较时，我们既需要使用in、all、any关键字，又需要使用圆括号将待比较的多个属性括起来（如果不理解本段话，建议回看上文的多行单列子查询和单行多列子查询）。

那么如何判断子查询是否是多行多列子查询呢？说一下，如果select时我们指明了要显示属性1和属性2，那么当前子查询一定是多列查询；如果发现子查询的查询结果有多行，则当前子查询就是多行查询。最后一结合就是多行多列子查询了。

在from子句中使用子查询

事实上和下图1红框中的emp表一样，下图1蓝框中的SQL语句【select * from emp where deptno=20;】本质上也表示一张表，并且这张表的表名就是【select * from emp where deptno=20;】，如下图2即可证明这一点。这两张表的唯一区别就是emp表是真实存在于磁盘上的，而【select * from emp where deptno=20;】表则不是，其只存在于内存上。

说一下，在SQL语句select * from【select * from emp where deptno=20;】中，因为【select * from emp where deptno=20;】是表名称，所以其可以作为from子句的对象，而同时又因为【select * from emp where deptno=20;】是子查询，所以就把select * from【select * from emp where deptno=20;】这样的SQL语句叫做在from子句中使用子查询了。

注意，如下图2的第一个红框处所示，在from子句中使用子查询以让子查询作为表名称时，必须给该表（即子查询）起一个别名，否则就会报错。

所以往后我们就要知道，只要select语句A能查出一张表A，则不管该select语句A中嵌套了多少个select，select语句A本身都就是表A的表名称。

图1如下。
图2如下。

那么问题来了，在from子句中使用子查询有什么用呢？

答案是，在讲解【在from子句中使用子查询】部分前所讲解的所有子查询，其都是单表查询（不信的话，你往上翻回看上文所讲的子查询），在业务不复杂的时候【单表查询+子查询】这样的组合还能解决一下问题，但当业务变得复杂后，单表查询就不够用了，而需要进行多表查询，同时因为有时候我们需要的表并不存在于磁盘上，而需要我们通过子查询生成我们需要的临时表，所以这时就需要我们在from子句中使用子查询了。换言之，为什么要在from子句中使用子查询呢？就是因为我们需要通过from子句将子查询生成的临时表和其他表组合在一起生成笛卡尔积，以解决更复杂的业务。

接下来咱们做一些在from子句中使用子查询的实操，如下。

显示每个高于自己部门平均工资的员工的姓名、部门、工资和部门的平均工资（即只要一个员工的工资高于他所在部门的平均工资，则将该员工的相关信息显示出来），如下：

因为员工姓名（即属性ename）、部门编号（即属性deptno）、工资（即属性sal）都存在于emp表中，所以我们选择select from emp查询emp表。

然后因为当前需要每个部门的平均工资，所以我们先如下图1上半部分的右边所示，先求出每个部门的平均工资，然后如下图1的下半部分所示，取emp表和子查询生成的部门平均工资表的笛卡尔积，可以看到这样一来，如下图1黄框处和绿框处所示，每个员工的工资信息和部门的平均工资信息就位于同一张表中了，这时我们只需先在笛卡尔积中筛选出有意义的数据（像下图1红框处这样，一个员工属于10号部门，则给该员工拼接上10号部门的平均工资信息才有意义，如果此时给该员工拼接上20或30号部门的信息，则就没有意义），然后再筛选出员工工资大于部门的平均工资的员工即可成功解题了。如下图2就是用于解题的SQL语句。

图1如下。
图2如下。

显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资，如下：

因为员工姓名（即属性ename）、工资（即属性sal）、部门编号（即属性deptno）都存在于emp表中，所以我们选择select from emp查询emp表。

然后因为当前需要每个部门的最高工资，所以我们先如下图1上半部分的右边所示，先求出每个部门的最高工资，然后如下图1的下半部分所示，取emp表和子查询生成的部门最高工资表的笛卡尔积，可以看到这样一来，如下图1黄框处和绿框处所示，每个员工的工资信息和部门的最高工资信息就位于同一张表中了，这时我们只需先在笛卡尔积中筛选出有意义的数据（像下图1红框处这样，一个员工属于10号部门，则给该员工拼接上10号部门的最高工资信息才有意义，如果此时给该员工拼接上20或30号部门的信息，则就没有意义），然后再筛选出员工工资等于部门的最高工资的员工即可成功解题了。如下图2就是用于解题的SQL语句。

图1如下。
图2如下。

显示每个部门的部门名、部门编号、所在地址和人员数量，如下：

因为部门名（即属性dname）、部门编号（即属性deptno）、部门所在地（即属性loc）都存在于dept表中，所以我们选择select from dept查询dept表。

然后因为当前需要每个部门的人员数量，所以我们先如下图1上半部分的右边所示，先求出每个部门的人员数量（因为员工即属性ename只存在于emp表中，所以求每个部门的人员数量时需要select from查询emp表），然后如下图1的下半部分所示，取dept表和子查询生成的部门人数表的笛卡尔积，可以看到这样一来，如下图1的下半部分所示，每个部门的部门名、部门编号、所在地址和人员数量信息就都位于同一张表中了，这时我们只需在笛卡尔积中筛选出有意义的数据即可成功解题了（像下图1红框处这样，一个部门的编号是10号，则给该部门拼接上10号部门的总人数信息才有意义，如果此时给该部门拼接上20或30号部门的总人数信息，则就没有意义）。如下图2就是用于解题的SQL语句。

图1如下。
图2如下。

（结合下图思考）走到这里，我们就做了更多关于多表查询的练习了，所以我们就能更加深刻地理解多表查询的应用场景了，即如果要同时显示属性A和属性B，但属性A和属性B被分散在表1和表2中，那就可以通过多表查询将表1和表2拼接在一起形成笛卡尔积，这时在笛卡尔积中就同时有属性A和属性B了，这时只需对笛卡尔积做数据清洗以清洗掉无意义的数据，然后就可以按需进行同时显示属性A和属性B了。

合并查询

咱们直接上实操演示什么是合并查询，如下。

要求：在一张表中显示工资大于2500或职位是MANAGER的员工，如下：

因为工资（即属性sal）、职位（即属性job）都存在于emp表中，所以我们选择select from emp查询emp表。

然后接下来有两种方式解决这题，方式一，直接通过where子句把数据晒出来即可，如下。

方式二，先查询工资大于2500的员工，再查询职位是MANAGER的员工，最后通过用于做合并查询的union关键字将两个查询结果合并起来，如下图1第三个红框中的数据就是合并后的结果。

说一下，在下图1中查询工资大于2500的员工时，可以发现有些工资大于2500的员工的职位同时也是MANAGER，这就说明了未来在查询职位是MANAGER的员工时得到的数据中一定会有数据和【查询工资大于2500的员工时得到的数据】重复，也就是说，在下图1第一个红框中一定存在和下图1第二个红框中完全相同的数据（如黄框标明的数据就是重复数据）。但最后我们通过union关键字把第一个红框中的数据和第二个红框中的数据合并起来的时候，如下图1第三个红框处所示，却发现其中没有重复的数据，所以笔者想说的是，union关键字会自动完成去重，如果你不想要去重后的结果，那在合并时可以如下图2所示使用union all关键字，如下图2的两个黄框处所示，此时就有重复数据了。

图1如下。
图2如下。

然后需要补充的细节是，如下图1所示，在通过union或union all关键字做合并查询时，在两个被合并的查询结果中，属性列表中的属性个数一定要相等，否则会合并失败；然后要说的是，如下图2所示，在通过union或union all关键字做合并查询时，在两个被合并的查询结果中，属性列表中的属性不仅要个数相等，而且还要求属性一致、顺序一致，否则MySQL会无脑合并，合并出一些无意义的数据。