在本教程中,将了解SQL Server子查询以及如何使用子查询来查询数据。
1. SQL Server子查询简介
子查询是嵌套在另一个语句(如:SELECT,INSERT,UPDATE或DELETE)中的查询。
现在来看下面的例子,考虑示例数据库中的orders
和 customers
表,它们的结构和关系如下:
以下语句显示如何在SELECT
语句的WHERE子句中使用子查询来查找位于纽约(New York
)的客户的销售订单:
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;
执行上面查询语句,得到以下结果:
在此示例中,以下语句是子查询:
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
执行上面查询语句,得到以下结果:
请注意,必须始终将子查询的SELECT
查询括在括号()
中。
子查询也称为内部查询或内部选择,而包含子查询的语句称为外部选择或外部查询:
SQL Server执行上面的整个查询示例,如下所示:
首先,它执行子查询以获取城市为New Year
的客户的客户标识号列表。
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
其次,SQL Server替换IN运算符中子查询返回的客户标识号,并执行外部查询以获取最终结果集。
如您所见,通过使用子查询,可以将两个步骤组合在一起。 子查询消除了选择客户标识号并将其插入外部查询的需要。 此外,只要客户数据发生变化,查询本身就会自动进行调整。
2. 嵌套子查询
子查询可以嵌套在另一个子查询中。 SQL Server最多支持32
个嵌套级别。 请考虑以下示例:
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执行以下子查询以获取品牌名称为'上海永久'
和'凤凰'
的品牌标识号列表:
SELECT
brand_id
FROM
production.brands
WHERE
brand_name = '上海永久'
OR brand_name = '凤凰';
执行上面查询语句,得到以下结果:
第二步,SQL Server计算属于这些品牌的所有产品的平均价格。
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (1,2)
第三步,SQL Server查找价格高于'上海永久'
和'凤凰'
品牌的所有产品的平均定价的产品。
3. SQL Server子查询类型
可以在许多地方使用子查询:
3.1. SQL Server子查询用于代替表达式
如果子查询返回单个值,则可以在使用表达式的任何位置使用它。
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
运算符一起使用的子查询返回一组零个或多个值。 子查询返回值后,外部查询将使用它们。
以下查询查找出售的所有山地自行车和公路自行车产品的名称。
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
运算符引入子查询的语法:
scalar_expression comparison_operator ANY (subquery)
假设子查询返回值为:v1
,v2
,... vn
的列表。 如果比较scalar_expression
中的一个评估为TRUE
,则ANY
运算符返回TRUE
; 否则,它返回FALSE
。
例如,以下查询查找价格大于或等于任何产品品牌的平均价格的产品。
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price >= ANY (
SELECT
AVG (list_price)
FROM
production.products
GROUP BY
brand_id
)
执行上面示例代码,得到以下结果:
对于每个品牌,子查询都会找到平均价格。 外部查询使用这些最大价格并确定哪个单独产品的清单价格大于或等于任何品牌的平均价格。
SQL Server子查询与ALL运算符一起使用ALL
运算符与ANY
运算符具有相同的语法:
scalar_expression comparison_operator ALL (subquery)
如果所有比较scalar_expression
的计算结果为TRUE
,则ALL
运算符返回TRUE
; 否则,它返回FALSE
。
以下查询查找列表价格大于或等于子查询返回的平均价格的产品:
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运算符引入的子查询的语法:
WHERE [NOT] EXISTS (subquery)
如果子查询返回结果,则EXISTS
运算符返回TRUE
; 否则返回FALSE
。
另一方面,NOT EXISTS
与EXISTS
运算符相反。
以下查询查找2017
年购买产品的客户:
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
年未购买任何产品的客户。
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;
执行上面查询语句,得到以下结果: