>[danger] ## [子查询]- 介绍子查询概念并向您展示如何使用各种子查询类型来查询数据。
#### 1\. SQL Server子查询简介
子查询是嵌套在另一个语句(如：SELECT，INSERT，UPDATE或DELETE中的查询。
现在来看下面的例子，考虑示例数据库中的`orders` 和 `customers`表，它们的结构和关系如下：
以下语句显示如何在`SELECT`语句的WHERE子句中使用子查询来查找位于纽约(`New York`)的客户的销售订单：
~~~sql
SELECT
order_id,
order_date,
customer_id
FROM
sales.orders
WHERE
customer_id IN (
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
)
ORDER BY
order_date DESC;
~~~
执行上面查询语句，得到以下结果：
在此示例中，以下语句是子查询：
~~~sql
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
~~~
执行上面查询语句，得到以下结果：
请注意，必须始终将子查询的`SELECT`查询括在括号`()`中。
子查询也称为内部查询或内部选择，而包含子查询的语句称为外部选择或外部查询：
SQL Server执行上面的整个查询示例，如下所示：
首先，它执行子查询以获取城市为`New Year`的客户的客户标识号列表。
~~~sql
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
~~~
其次，SQL Server替换[IN](http://www.yiibai.com/sqlserver/sql-server-in.html "IN")运算符中子查询返回的客户标识号，并执行外部查询以获取最终结果集。
如您所见，通过使用子查询，可以将两个步骤组合在一起。 子查询消除了选择客户标识号并将其插入外部查询的需要。 此外，只要客户数据发生变化，查询本身就会自动进行调整。
#### 2\. 嵌套子查询
子查询可以嵌套在另一个子查询中。 SQL Server最多支持`32`个嵌套级别。 请考虑以下示例：
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price > (
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (
SELECT
brand_id
FROM
production.brands
WHERE
brand_name = '上海永久'
OR brand_name = '凤凰'
)
)
ORDER BY
list_price;
~~~
执行上面查询语句，得到以下结果：
上面语句有些复杂，如果第一眼没有看明白，没有关系，可通过以下步骤一步步地理解。
**首先**，SQL Server执行以下子查询以获取品牌名称为`'上海永久'`和`'凤凰'`的品牌标识号列表：
~~~sql
SELECT
brand_id
FROM
production.brands
WHERE
brand_name = '上海永久'
OR brand_name = '凤凰';
~~~
执行上面查询语句，得到以下结果：
**第二步**，SQL Server计算属于这些品牌的所有产品的平均价格。
~~~sql
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (1,2)
~~~
**第三步**，SQL Server查找价格高于`'上海永久'`和`'凤凰'`品牌的所有产品的平均定价的产品。
#### 3\. SQL Server子查询类型
可以在许多地方使用子查询：
* 代替表达
* 使用IN或NOT IN
* ANY或ALL
* 有EXISTS或`NOT EXISTS`语句中。
* 在UPDATE，DELETE或INSERT语句中。
**3.1. SQL Server子查询用于代替表达式**
如果子查询返回单个值，则可以在使用表达式的任何位置使用它。
~~~sql
SELECT
order_id,
order_date,
(
SELECT
MAX (list_price)
FROM
sales.order_items i
WHERE
i.order_id = o.order_id
) AS max_list_price
FROM
sales.orders o
order by order_date desc;
~~~
执行上面查询语句，得到以下结果：
**3.2. SQL Server子查询与IN运算符**
与`IN`运算符一起使用的子查询返回一组零个或多个值。 子查询返回值后，外部查询将使用它们。
以下查询查找出售的所有山地自行车和公路自行车产品的名称。
~~~sql
SELECT
product_id,
product_name
FROM
production.products
WHERE
category_id IN (
SELECT
category_id
FROM
production.categories
WHERE
category_name = 'Mountain Bikes'
OR category_name = 'Road Bikes'
);
~~~
执行上面查询语句，得到以下结果：
此查询分两步进行评估：
* 首先，内部查询返回与名称`Mountain Bikes`和`Road Bikes`相匹配的类别标识号列表。
* 其次，这些值被替换为外部查询，外部查询查找具有类别标识号与列表中的一个值匹配的产品名称。
**3.2. SQL Server子查询与ANY运算符一起使用**
使用`ANY`运算符引入子查询的语法：
~~~sql
scalar_expression comparison_operator ANY (subquery)
~~~
假设子查询返回值为：`v1`，`v2`，`... vn`的列表。 如果比较`scalar_expression`中的一个评估为`TRUE`，则`ANY`运算符返回`TRUE`; 否则，它返回`FALSE`。
例如，以下查询查找价格大于或等于任何产品品牌的平均价格的产品。
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price >= ANY (
SELECT
AVG (list_price)
FROM
production.products
GROUP BY
brand_id
)
~~~
执行上面示例代码，得到以下结果：
子查询与ANY运算符一起使用
对于每个品牌，子查询都会找到平均价格。 外部查询使用这些最大价格并确定哪个单独产品的清单价格大于或等于任何品牌的平均价格。
**SQL Server子查询与ALL运算符一起使用**
`ALL`运算符与`ANY`运算符具有相同的语法：
~~~sql
scalar_expression comparison_operator ALL (subquery)
~~~
如果所有比较`scalar_expression`的计算结果为`TRUE`，则`ALL`运算符返回`TRUE`; 否则，它返回`FALSE`。
以下查询查找列表价格大于或等于子查询返回的平均价格的产品：
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price >= ALL (
SELECT
AVG (list_price)
FROM
production.products
GROUP BY
brand_id
)
~~~
执行上面查询语句，得到以下结果：
**3.3. SQL Server子查询与EXISTS或NOT EXISTS一起使用**
以下语句使用EXISTS运算符引入的子查询的语法：
~~~sql
WHERE [NOT] EXISTS (subquery)
~~~
如果子查询返回结果，则`EXISTS`运算符返回`TRUE`; 否则返回`FALSE`。
另一方面，`NOT EXISTS`与`EXISTS`运算符相反。
以下查询查找`2017`年购买产品的客户：
~~~sql
SELECT
customer_id,
first_name,
last_name,
city
FROM
sales.customers c
WHERE
EXISTS (
SELECT
customer_id
FROM
sales.orders o
WHERE
o.customer_id = c.customer_id
AND YEAR (order_date) = 2017
)
ORDER BY
first_name,
last_name;
~~~
执行上面查询语句，得到以下结果：
如果使用`NOT EXISTS`，可以查找`2017`年未购买任何产品的客户。
~~~sql
SELECT
customer_id,
first_name,
last_name,
city
FROM
sales.customers c
WHERE
NOT EXISTS (
SELECT
customer_id
FROM
sales.orders o
WHERE
o.customer_id = c.customer_id
AND YEAR (order_date) = 2017
)
ORDER BY
first_name,
last_name;
~~~
执行上面查询语句，得到以下结果：
>[danger] ## [相关子查询] - 介绍相关子查询概念以及如何使用。
相关子查询是使用外部查询的值的[子查询](http://www.yiibai.com/sqlserver/sql-server-subquery.html "子查询")。 换句话说，它取决于外部查询的值。 由于这种依赖性，相关子查询不能作为简单子查询独立执行。
此外，对外部查询评估的每一行重复执行一次相关子查询。相关子查询也称为**重复子查询**。
请考虑示例数据库中的以下`products`表：
以下示例查找价格等于其类别的最高价格的产品。
~~~sql
SELECT
product_name,
list_price,
category_id
FROM
production.products p1
WHERE
list_price IN (
SELECT
MAX (p2.list_price)
FROM
production.products p2
WHERE
p2.category_id = p1.category_id
GROUP BY
p2.category_id
)
ORDER BY
category_id,
product_name;
~~~
执行上面查询语句，得到以下结果:
在此示例中，对于由外部查询评估的每个产品，子查询查找其类别中所有产品的最高价格。 如果当前产品的价格等于其类别中所有产品的最高价格，则产品将包含在结果集中。 此过程将继续进行下一个产品，依此类推。
>[danger] ## [EXISTS] - 测试子查询返回的行的存在性。
**1\. 带子查询的EXISTS返回NULL示例**
请参阅示例数据库中的`customers`表。
Exists返回NULL示例
以下示例返回`customers`表中的所有行：
~~~sql
SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
EXISTS (SELECT NULL)
ORDER BY
first_name,
last_name;
~~~
执行上面查询语句，得到以下结果：
在此示例中，子查询返回包含`NULL`的结果集，这也导致`EXISTS`运算符计算为`TRUE`。
**1.2. EXISTS带有相关子查询示例**
考虑以下`customers`和`orders`表，它们的结构如下所示：
以下示例查找已下过两个以上订单的所有客户：
~~~sql
SELECT
customer_id,
first_name,
last_name
FROM
sales.customers c
WHERE
EXISTS (
SELECT
COUNT (*)
FROM
sales.orders o
WHERE
customer_id = c.customer_id
GROUP BY
customer_id
HAVING
COUNT (*) > 2
)
ORDER BY
first_name,
last_name;
~~~
执行上面查询语句，得到以下结果：
EXISTS带有相关子查询示例
在这个例子中，我们有一个相关的子查询，它返回下过两个以上订单的客户。
如果客户下达的订单数小于或等于`2`，则子查询返回一个空结果集，该结果集导致`EXISTS`运算符计算为`FALSE`。
根据`EXISTS`运算符的结果，客户是否包含在结果集中。
**1.3. EXISTS 与 IN示例**
以下语句使用`IN`运算符查找城市为`San Jose`的客户订单：
~~~sql
SELECT
*
FROM
sales.orders
WHERE
customer_id IN (
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'San Jose'
)
ORDER BY
customer_id,
order_date;
~~~
以下语句使用返回相同结果的`EXISTS`运算符：
~~~sql
SELECT
*
FROM
sales.orders o
WHERE
EXISTS (
SELECT
customer_id
FROM
sales.customers c
WHERE
o.customer_id = c.customer_id
AND city = 'San Jose'
)
ORDER BY
o.customer_id,
order_date;
~~~
执行上面查询语句，得到以下结果：
EXISTS 与 IN示例
**1.4. EXISTS与JOIN**
`JOIN`子句从另一个表返回行记录，`EXISTS`运算符返回`TRUE`或`FALSE`。
可以使用`EXISTS`运算符来测试子查询是否返回行，并尽快进行短路。 另一方面，使用`JOIN`将结果集与另一个相关表中的列组合来扩展结果集。
>[danger] ## [ANY]- 将值与子查询返回的单列值集进行比较，如果值与集合中的任何值匹配则并返回`TRUE`。
## SQL Server ANY运算符简介
`ANY`运算符是一个逻辑运算符，它将标量值与子查询返回的单列值集进行比较。
以下是`ANY`运算符的语法：
~~~sql
scalar_expression comparison_operator ANY (subquery)
~~~
在上面语法中，
* `scalar_expression` - 是任何有效的表达式。
* `comparison_operator` - 是任何比较运算符。
* `subquery`是一个SELECT语句，它返回单个列的结果集，其数据与标量表达式的数据类型相同。
假设子查询返回值列表`v1，v2，...，vn`。 如果`ANY`比较(`scalar_expression，vi`)返回`TRUE`，则`ANY`运算符返回`TRUE`。 否则它返回`FALSE`。
请注意，`SOME`运算符等效于`ANY`运算符。
## SQL Server ANY运算符示例
请参阅示例数据库中的以下`products`表，结构如下所示：
以下示例查找销售订单中销售数量超过`2`个的产品：
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
product_id = ANY (
SELECT
product_id
FROM
sales.order_items
WHERE
quantity >= 2
)
ORDER BY
product_name;
~~~
执行上面查询语句，得到以下结果：
>[danger] ## [ALL]- 将值与子查询返回的单列值集进行比较，如果值与集合中的所有值匹配并返回`TRUE`。
SQL Server `ALL`运算符是一个逻辑运算符，它将标量值与子查询返回的单列值列表进行比较。
以下是`ALL`运算符语法：
~~~sql
scalar_expression comparison_operator ALL ( subquery)
~~~
在上面语法中，
* `scalar_expression`是任何有效的表达式。
* `comparison_operator`是任何有效的比较运算符，包括等于(`=`)，不等于(`<>`)，大于(`>`)，大于或等于(`>=`)，小于(`<`)，小于或等于(`<=`)。
* 括号内的子查询(`subquery`)是一个[SELECT](http://www.yiibai.com/sqlserver/sql-server-select.html "SELECT")语句，它返回单个列的结果。 此外，返回列的数据类型必须与标量表达式的数据类型相同。
如果所有比较对`(scalar_expression，v)`的计算结果为`TRUE`，则`ALL`运算符返回`TRUE`; `v`是单列结果中的值。
如果其中一对`(scalar_expression，v)`返回`FALSE`，则`ALL`运算符返回`FALSE`。
## SQL Server ALL运算符示例
请考虑[示例数据库](https://www.yiibai.com/sqlserver/sql-server-sample-database.html "示例数据库")中的以下`products`表。
以下查询语句返回每个品牌的产品平均价格：
~~~sql
SELECT
AVG (list_price) avg_list_price
FROM
production.products
GROUP BY
brand_id
ORDER BY
avg_list_price;
~~~
执行上面查询语句，得到以下结果：
**1\. scalar\_expression > ALL ( subquery )**
如果`scalar_expression`大于子查询返回的最大值，则表达式返回`TRUE`。
例如，以下查询查找价格大于所有品牌产品的平均价格的产品：
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price > ALL (
SELECT
AVG (list_price) avg_list_price
FROM
production.products
GROUP BY
brand_id
)
ORDER BY
list_price;
~~~
执行上面查询语句，得到以下结果：
**2\. scalar\_expression < ALL ( subquery )**
如果标量表达式(`scalar_expression`)小于子查询(`subquery`)返回的最小值，则表达式求值为`TRUE`。
以下示例按品牌查找价格低于平均价格中最低价格的产品：
~~~sql
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price < ALL (
SELECT
AVG (list_price) avg_list_price
FROM
production.products
GROUP BY
brand_id
)
ORDER BY
list_price DESC;
~~~
执行上面查询语句，得到以下结果：
类似地，也可以使用以下比较运算符之一来使用`ALL`运算符，例如等于(`=`)，大于或等于(`>=`)，小于或等于(`<=`)和不等于(`<>`)。
- 第三章-数据库
- 3.1 SQL Server简介及安装
- 3.2 SQL Server示例数据库
- 3.3 SQL Server 加载示例
- 3.3 SQL Server 中的数据类型
- 3.4 SQL Server 数据定义语言DDL
- 3.5 SQL Server 修改数据
- 3.6 SQL Server 查询数据
- 3.7 SQL Server 连表
- 3.8 SQL Server 数据分组
- 3.9 SQL Server 子查询
- 3.10.1 SQL Server 集合操作符
- 3.10.2 SQL Server聚合函数
- 3.10.3 SQL Server 日期函数
- 3.10.4 SQL Server 字符串函数