你好，我是悟空。
本文已收录至Github，欢迎 Star：https://github.com/Jackson0714/PassJava-Learning
个人网站：www.passjava.cn
概述
本篇主要是子查询基础的总结。
![]()
关键词解释：
外部查询：查询结果集返回给调用者
内部查询：查询结果集返回给外部查询。
独立子查询：独立子查询独立于其外部查询的子查询，可以单独运行子查询。在逻辑上，独立子查询在执行外部查询之前先执行一次，接着外部查询再使用子查询的结果继续进行查询。
相关子查询：引用了外部查询中出现的表的子查询，查询要依赖于外部查询，不能独立地调用它。在逻辑上，子查询会为每个外部行单独计算一次。
标量子查询：返回单个值的子查询。标量子查询可以出现在外部查询中期望使用单个值的任何地方。
多值子查询：在一个列中
为什么要使用子查询？
可以避免在查询解决方案中把操作分成多个步骤，并在变量中保存中间查询结果的需要。
一、独立子查询
1. 独立标量子查询
例子：从 HR.Employees 表中返回 empid 最大的员工信息。
可以分两步：
a. 定义一个变量 maxid ，通过独立标量子查询查询出 empid 最大的员工的 empid，然后将这个 empid 保存到变量@maxid 中
b. 在 WHERE 条件中过滤出 empid = @maxid 的记录
DECLARE @maxid AS INT = ( SELECT MAX(empid)
FROM HR.Employees
)
SELECT *
FROM HR.Employees
WHERE empid = @maxid
更简单的方法是嵌套子查询，只需要一条查询语句就可以查询出 empid 最大的员工信息
SELECT *
FROM HR.Employees
WHERE empid = ( SELECT MAX(empid)
FROM HR.Employees
)
注意：
-
对于有效的标量子查询，它的返回值不能超过一个，如果标量子查询返回了多个值，在运行时则可能会失败。
-
如果标量子查询没有返回任何值，其结果就转换为 NULL，和 NULL 行进行比较得到的是 UNKNOWN，查询过滤器不会返回任何让过滤表达式计算结果为 UNKNOWN 的行。
2. 独立多值子查询
(1) 多值子查询的语法格式
<标量表达式、> IN ( <多值子查询、> )
例子：返回 title 包含 manager 的雇员处理过的订单的信息
方案一：独立多值子查询
SELECT *
FROM Sales.Orders
WHERE empid IN ( SELECT empid
FROM HR.Employees
WHERE HR.Employees.title LIKE '%Manager' )
方案二：内联接查询
SELECT *
FROM Sales.Orders
INNER JOIN HR.Employees ON Sales.Orders.empid = HR.Employees.empid
WHERE HR.Employees.title LIKE '%Manager'
类似地，很多地方既可以用子查询也可以用联接查询来解决问题。数据库引擎对两种查询的解释有时候是一样的，而在另外一些情况下，对二者的解释则是不同的。可以先用一种查询解决问题，如果性能不行，再尝试用联接替代子查询，或用子查询替代联接。
3. 子查询之 distinct 关键字
当我们想要剔除掉子查询中的重复值时，会想到在子查询中不必指定 distinct 关键字，其实是没有必要的，因为数据库引擎会帮助我们删除重复的值，而不用我们显示指定 distinct 关键字。
二、相关子查询
1. 相关子查询
什么是相关子查询：引用了外部查询中出现的表的列，依赖于外部查询，不能独立地运行子查询。在逻辑上，子查询会为每个外部行单独计算一次。
例子：查询每个客户返回在他参与活动的最后一天下过的所有订单。
期望结果：
![]()
影响行数：90
- 首先用独立标量子查询查询出最大的订单日期，返回给外部查询
SELECT MAX(orderdate)
FROM sales.Orders AS O2
-
外部查询用 O1.orderdate 进行过滤，过滤出等于最大订单日期的订单
-
因为要查询出每个客户参与的订单，所以将独立标量子查询改成相关子查询，用子查询 O2.custid 与外查询 O1.custid 关联。
对于 O1 中每一行，子查询负责返回当前客户的最大订单日期。如果 O1 中某行的订单日期和子查询返回的订单日期匹配，那么 O1 中的这个订单日期就是当前客户的最大的订单日期，在这种情况下，查询便会返回 O1 表中的这个行。
SELECT MAX(orderdate)
FROM sales.Orders AS O2
WHERE O2.custid = O1.custid
综合上面的步骤，得到下面的查询语句：
SELECT orderid,orderdate,custid
FROM sales.Orders AS O1
WHERE O1.orderdate = ( SELECT MAX(orderdate)
FROM sales.Orders AS O2
WHERE O2.custid = O1.custid
)
2.EXISTS 谓词
- <外查询> WHERE EXISTS ( 子查询 )
- 它的输入是一个子查询，：如果子查询能够返回任何行，改谓词则返回 TRUE，否则返回 FALSE.
- 如果子查询查询结果又多条，SQL SERVER 引擎查询出一条记录后，就会立即返回，这种处理方式叫做短路处理。
- Exist 谓词只关心是否存在匹配行，而不考虑 SELECT 列表中指定的列，所有使用 SELECT * FROM TABLE，并没有什么负面影响，但是为了展开代码的列名会有少少量的开销，但是还是推荐使用通配符，查询语句应该尽可能保持自然和直观，除非有非常令人信服的理由，才可以牺牲代码在这方面的要求。
- NOT EXISTS 谓词是 EXISTS 谓词的反面
三、练习题
1. 写一条查询语句，返回 Orders 表中活动的最后一天生成的所有订单。
期望结果：
![]()
本题考察独立子查询的基本用法，首先用独立子查询返回最后一天的日期，然后外部查询过滤出订单日期等于最后一天的所有订单。
SELECT orderid ,
orderdate ,
custid ,
empid
FROM Sales.Orders
WHERE orderdate = ( SELECT MAX(orderdate)
FROM Sales.Orders
)
2. 查询出拥有订单数量的最多的客户下过的所有订单。
期望结果：
![]()
本题考察独立子查询的用法，和第一题类似，分两个步骤：
（1）先用子查询查询出订单数量最多的客户 id
（2）然后将 id 返回给外部查询，外部查询通过客户 id 过滤出客户下过的所有订单
方案一：独立标量子查询
SELECT custid ,
orderid ,
orderdate ,
empid
FROM Sales.Orders
WHERE custid = ( SELECT TOP ( 1 ) WITH TIES
O.custid
FROM Sales.Orders AS O
GROUP BY custid
ORDER BY COUNT(*) DESC
)
注意：
TOP ( 1 ) WITH TIES O.custid
查找出排序后与第一条记录 O.custid 相等的所有行
因为下过订单数最多的客户的总订单数是 31，且只有一个客户（custid=71），所以最后的查询结果中只有 custid=71 的客户下过的所有订单。
3. 查询出 2008 年 5 月 1 号（包括这一天）以后没有处理过订单的雇员。
期望结果：
![]()
本题考察独立子查询的用法，本题也可以采用两步来查询出结果。
（1）首先用子查询返回所有 2008 年 5 月 1 号（包括这一天）以后处理过订单的雇员，将这些雇员的 empid 返回给外部查询
（2）然后外部查询用 NOT IN 过滤出所有 2008 年 5 月 1 号（包括这一天）之后没有处理过订单的雇员
方案一：独立标量子查询 + NOT IN
SELECT *
FROM HR.Employees
WHERE empid NOT IN ( SELECT empid
FROM Sales.Orders
WHERE orderdate >= '20080501' )
4. 查询 2007 年下过订单，而在 2008 年没有下过订单的客户
期望输出：
![]()
方案一：内联接+独立标量子查询
- 查询出 20070101~20071231 所有下过订单的客户集合 Collection1
SELECT DISTINCT C.custid,companyname FROM Sales.Orders O
INNER JOIN Sales.Customers AS C ON C.custid = O.custid
WHERE (orderdate <= '20071231' AND orderdate >= '20070101')
- 查询出 20080101~20081231 所有下过订单的客户结合 Collection2
SELECT C.custid FROM Sales.Orders O
INNER JOIN Sales.Customers AS C ON C.custid = O.custid
WHERE (orderdate <= '20081231' AND orderdate >= '20080101')
3.Collection1 不包含 Collection2 的子集就是 2007 年下过订单而在 2008 年下过订单的客户
SELECT DISTINCT C.custid,companyname FROM Sales.Orders O
INNER JOIN Sales.Customers AS C ON C.custid = O.custid
WHERE (orderdate <= '20071231' AND orderdate >= '20070101')
AND C.custid NOT IN
(
SELECT C.custid FROM Sales.Orders O
INNER JOIN Sales.Customers AS C ON C.custid = O.custid
WHERE (orderdate <= '20081231' AND orderdate >= '20080101')
)
方案二：相关子查询 EXISTS+NOT EXISTS
-
查询出 20070101~20071231 所有下过订单的客户集合 Collection1
-
查询出 20080101~20081231 所有下过订单的客户结合 Collection2
3.Collection1 不包含 Collection2 的子集就是 2007 年下过订单而在 2008 年下过订单的客户
SELECT C.custid ,
companyname
FROM Sales.Customers AS C
WHERE EXISTS ( SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND ( orderdate <= '20071231'
AND orderdate >= '20070101'
) )
AND NOT EXISTS ( SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND ( orderdate <= '20081231'
AND orderdate >= '20080101'
) )
由方案一和方案二，我们可以总结出：INNER JOIN+独立子查询可以用 Exists+相关子查询代替
5. 查询订购了第 12 号产品的客户
期望结果：
![]()
方案一：内联接多张表
SELECT DISTINCT
C.custid ,
companyname
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O ON C.custid = O.custid
INNER JOIN Sales.OrderDetails AS D ON O.orderid = D.orderid
WHERE D.productid = '12'
方案二：嵌套相关子查询
SELECT C.custid ,
companyname
FROM Sales.Customers AS C
WHERE EXISTS ( SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND EXISTS ( SELECT *
FROM Sales.OrderDetails AS D
WHERE D.orderid = O.orderid
AND D.productid = '12' ) )
参考资料：
《SQL2008 技术内幕：T-SQL 语言基础》
欢迎关注我的公众号：「悟空聊架构」
作者简介：8 年互联网职场老兵｜全栈工程师｜90 后超级奶爸｜开源践行者｜公众号万粉原创号主。 蓝桥签约作者，著有《JVM 性能调优实战》专栏，手写了一套 7 万字 SpringCloud 实战总结和 3 万字分布式算法总结。 欢迎关注我的公众号「悟空聊架构」，免费获取资料学习。
我是悟空，努力变强，变身超级赛亚人！
