一步一步学MySQL----16 多表数据记录查询之子查询

16.1 为什么使用子查询

日常工作中，经常会用到多表查询，而在进行多表查询时，首先会对两个表进行笛卡尔积操作，然后再选取符合匹配条件的数据记录。在进行笛卡尔积操作的时候，会生成两个数据表中数据记录数的乘积条数据记录。如果这两个表的数据记录比较大，则在进行笛卡尔积操作时就会造成死机。

---

因此，对于有经验的用户，首先会通过统计函数查看所操作笛卡尔积后的数据记录数，然后才会进行多表查询。

因此，多表查询的一般步骤如下：

（1） 通过统计函数count()查询笛卡尔积后的数据记录数，如：

mysql> select count(*) from employee e, department d;

（2） 如果查询到的数据记录MySQL可接受，然后才进行多表查询，否则就应该考虑其他方式来实现。

针对笛卡尔积后的数据记录数远远大于MySQL可以接受的范围这个问题，MySQL提供了子查询操作来解决这个问题。

所谓子查询，就是指在一个查询语句中嵌套了其他的若干查询，即在一个select查询语句的where或from子句中包含另一个select查询语句。

在查询语句中，外层select查询语句称为主查询，where子句中的select查询语句称为子查询，也被称之为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含in、any、all和exists等关键字，除此之外还可能包含比较运算符。

理论上，子查询语句可以出现在查询语句的任何位置，但是实际应用中，子查询经常出现在where和from子句中。

• Where子句中的子查询：该位置处的子查询一般返回单行单列、多行单列、单行多列数据记录

• From子句中的子查询：该位置处的子查询一般返回多行多列数据记录，可以当作一张临时表。

16.2 单行单列的子查询

说明：当子查询的返回结果为单行单列的数据记录时，该子查询语句一般在祝查询语句的where子句中，通常包含比较运算符（“>”、“<”、“=”、“！=”等）

例如：执行SQL查询语句，在数据库company中，查询员工表中工资比Smith还要高的全部员工信息。

第一步：查询Smith员工的工资

mysql> select salary from employee where ename=”Smith”;

第二步：由于上述SQL语句返回单行单列，所以可以在主查询where关键字子句中出现

mysql> select * from employee where salary>(select salary from employee where ename=”Smith”);

16.3 单行多列的子查询

例如：执行SQL查询语句，在数据库company中，查询员工表中与James所在同一部门并且工资比James高的所有员工的信息。

第一步：查询James所在部门和工资

mysql> select depno,salary from employee where ename=”James”

第二步：由于上述SQL语句返回单行多列，所以可以在主查询的where子句中出现。

mysql> select * from employee where depno=(select depno from employee where ename=”james”) and salary>(select salary from employee where ename=”james”);

16.4 多行单列带有in的子查询

当主查询的条件是子查询的查询结果时，就可以通过关键字in来进行判断。相反，当主查询的条件不是子查询的查询结果时，就可以通过关键字not in来进行判断。

例如：执行SQL查询语句，在数据库company中，查询员工表中的数据记录，这些记录的部门编号必须在部门表中出现。

第一步：查询部门表的所有部门编号。

mysql> select depno from department;

第二步：由于上述语句返回的是多行单列，所以可以在主查询的where子句中出现。

mysql> select * from employee where depno in(select depno from department);

由于员工Kuli所在的部门不在部门表中，如果要通过子查询来查询其员工信息，如下：

mysql> select * from employee where depno not in(select depno from department);

16.5 多行单列带有any的子查询

关键字 any用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录，该关键字有三种匹配方式：

• =any：其功能与in一样
• >any（>=any）：比子查询中返回的数据记录中最小的还要大于（大于等于）的数据记录
• < any（<=any）：比子查询中返回的数据记录中最大的还要小于（小于等于）的数据记录

例如：执行SQL查询语句，在数据库company中，查询员工表中员工的姓名和工资，这些员工的工资不低于工种为Developer的工资

第一步：查询员工表中工种为Developer的工资

mysql> select salary from employee where job=”Developer”;

第二步：由于上述语句是多行单列，所以可以在主查询的where关键字子句中出现

mysql> select ename,salary from employee where salary>any(select salary from employee where job=”Developer”);

16.6 多行单列带有all的子查询

关键字all用来表示主查询的条件满足子查询返回查询结果中所有数据记录，该关键字有以下两种匹配方式：

• >all（>=all）：比子查询中返回数据记录中最大的还要大于（大于等于）数据记录；
• < all（<=all）：比子查询中返回数据记录中最小的还要小于（小于等于）数据记录；

例如：执行SQL查询语句，在数据库company中，查询员工表中的员工的姓名和工资，这些员工的工资高于工种为Developer的工资

第一步：查询员工表中工种为Developer的工资

mysql> select salary from employee where job=”Developer”;

第二步：由于上述语句是多行单列，所以可以在主查询的where关键字子句中出现

mysql> select ename,salary from employee where salary>all(select salary from employee where job=”Developer”);

16.7 多行单列带有exists的子查询

关键字exists是一个布尔类型，当返回结果集时为true，不能返回结果集时为false。

查询时，exists对外表采用遍历方式逐条查询，每次查询都会比较exists的条件语句，当exists里的条件语句返回记录时则条件为真，此时返回当前遍历到的记录；反之，如果exists里的条件语句不能返回记录时，则丢弃当前遍历到的记录。

例如：执行SQL查询语句，在数据库company中，查询部门表中的部门编号和部门名字，如果该部门没有员工，则显示该部门。

第一步：查询员工表和部门表，条件为员工表中的部门编号等于部门表中的部门编号

mysql> select * from employee e, department d where e.depno=d.depno;

第二步：查询有员工的部门编号和名称

mysql> select depno,dname from department d where exists(select * from employee e where e.depno=d.depno);

16.8 多行多列的子查询

当子查询的返回结果为多行多列数据记录时，该子查询语句一般会在主查询语句的from子句中，被当作一张临时表的方式来处理。

例如：执行SQL语句select，在数据库company中，查询员工表中各个部门的部门编号、部门名称、部门地址、员工人数和平均工资。

方法1：通过内连接来实现

mysql> select d.depno,d.dname,d.location,count(e.ename) number, avg(e.salary) average_salary from employee e inner join department d on e.depno=d.depno group by d.depno desc;

在运行过程中，关于笛卡尔积的数据记录数，可以通过以下SQL语句来查询

mysql> select count(*) from employee,department;

方法2：通过子查询来实现

mysql> select d.depno,d.dname,d.location,number,average_salary from department d inner join(select depno, count(empno) number, avg(salary) average_salary from employee e group by depno desc) e on d.depno=e.depno;

在运行过程中，关于笛卡尔积的数据记录数，可以通过以下SQL语句来查询

mysql> select count(*) from department,(select depno, count(empno) number, avg(salary) average_salary from employee group by depno) employee;

与内连接方式查询相比，子查询方式所操作的笛卡尔积数据记录远远小于前者，因此子查询方式的执行效率更高。