9.4 数据的嵌套查询

所谓嵌套查询，就是在一个查询语句中，嵌套进另一个查询语句，即，查询语句中可以使用另一个查询语句中得到的查询结果，子查询可以基于一张表或者多张表。子查询中常用的操作符有ANY、SOME、ALL、IN、EXISTS等。

9.4.1 使用比较运算符的子查询

子查询中可以使用的比较运算符有“<”“<=”“=”“>=”和“!=”等。

为演示子查询操作，下面创建员工部门信息（dept表），具体的表结构如下表：

dept表结构

在数据库mydatabase中，创建部门信息表：

CREATE TABLE dept
(deptID INT PRIMARY KEY,deptName VARCHAR(50),addr VARCHAR(100)
)

创建好后，向表中录入数据，如下表：

向数据表中添加数据记录，具体SQL语句如下：

INSERT INTO dept
VALUES (1,'行政部','行政楼101室'),(2,'人事部','行政楼102室'),(3,'销售部','行政楼103室'),(4,'财务部','行政楼104室')

例：在dept表中查询工作地点addr等于“行政楼101室”的部门编码deptID，然后在员工信息表employee中查询所有该部门编码的员工信息

SELECT * FROM employee WHERE deptcode=
(SELECT deptID FROM dept WHERE addr='行政楼101室')

例：在dept表中查询addr等于“行政楼101室”的部门编号deptID，然后在employee中查询所有非该部门的员工信息

SELECT * FROM employee WHERE deptcode !=
(SELECT deptID FROM dept WHERE addr='行政楼101室')

9.4.2 使用IN的子查询

IN关键字主要用来判断某个列是否在某个范围内，在子查询中，通常用在查询结果的前面，用于判断查询结果中是否有符合条件的数据，语法如下：

SELECT col_name1,col_name2,...
FROM table_name1
WHERE col_name 
IN (SELECT col_nameX FROM table_name2 WHERE conditions)

💡提示：
关键字IN后面是一个子查询，并且这个子查询只能返回一列值，另外，这个返回值的数据类型必须与IN前面列的数据类型一致。

例：在employee中，查询员工编号为“101”的员工所在的部门编码，再根据该编码，查询其部门名称：

SELECT deptName FROM dept
WHERE deptId IN
(SELECT deptcode FROM employee WHERE code='101')

这个例子说明，SQL在处理SELECT语句的时候，实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层的结果作为外层的条件使用。

SELECT语句中可以使用NOT IN运算符，其作为与IN 正好相反。

例：与前一个例子类似，但是在SELECT语句中使用NOT IN运算符

9.4.3 使用ANY的子查询

ANY关键字也是在子查询中经常使用，它可以用于比较某一列的值是否全部都大于ANY后面的子查询中查询结果的最小值，或者小于ANY后面子查询结果的最大值。

语法如下：

SELECT col_name1,col_name2,...
FROM table_name1
WHERE col_name operator ANY
(SELECT col_nameX FROM table_name2 WHERE conditions)

各参数说明：

• operator：就是比较运算符的统称，实际代码中可以是“<”“<=”“=”“>=”和“!=”等。

例：查询人事部员工工资大于销售部员工工资的员工信息

SELECT * FROM employee
WHERE salary > ANY
(SELECT salary FROM employee 
WHERE deptcode = (SELECT deptid FROM dept WHERE deptName = '销售部') )
AND deptcode=2

9.4.4 使用SOME的子查询

SOME关键字的用法与ANY关键字用法类似，但是意义不同。

SOME通常用于比较满足查询结果中的任意值，而ANY要满足所有值才可以。

语法如下：

SELECT col_name1,col_name2,...
FROM table_name1
WHERE col_name operator SOME 
(SELECT col_nameX FROM table_name2 WHERE conditions)

例：查询行政部和人事部所有员工的信息

SELECT * FROM employee
WHERE deptname = SOME ('人事部' or '行政部')

很显然，在表employee里，没有字段[deptname]，这个字段位于表“dept”中，所以需要使用子查询，先从“dept”中找到对应'人事部'和'行政部'的部门编码，再通过部门编码去表“employee”中取数。

上面的代码修改为：

SELECT * FROM employee
WHERE deptcode = SOME
(SELECT deptid FROM dept WHERE deptname = '人事部' OR  deptname = '行政部')

💡提示：
从结果可以发现，与IN关键字有完全相同的功能。
也就是说，在使用=SOME时，可以用IN替换

9.4.5 使用EXISTS的子查询

EXISTS关键字代表“存在”，它应用于子查询中，只要子查询返回的结果为空，那么返回就是TRUE，此时外层查询语句将被执行；否则就是FALSE，外层语句不执行。

通常情况下，EXSITS关键字用在WHERE子句中。

SELECT col_name1,col_name2,...
FROM table_name1
WHERE EXISTS (SELECT col_nameX FROM table_name2 WHERE conditions)

这里，当EXISTS后面的查询语句有结果时，那么EXISTS前面的查询就被执行，否则，不输出任何数据。

例：如果表dept中存在deptid=1的部门，就把表employee所有的员工信息全部查询出来

SELECT * FROM employee
WHERE EXISTS (SELECT 1 FROM dept WHERE deptid=1)

EXISTS关键字可以和条件表达式一起使用。

例：如果表dept中存在deptid=1的部门，就把表employee中salary大于3000元的记录查询出来

SELECT * FROM employee
WHERE salary > 3000
AND EXISTS (SELECT 1 FROM dept WHERE deptid=1)

NOT EXISTS与EXISTS使用方法相同，返回的结果相反。

例：如果表dept中不存在deptid=1的部门，就查询表employee中的所有记录

SELECT * FROM employee
WHERE NOT EXISTS (SELECT 1 FROM dept WHERE deptid=1)

因为表dept中存在，所以不查询，返回结果为空。

📢注意：
EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。