7.14 SQL Server子查询

SQL Server子查询

目录

• SQL Server子查询
  • 简介
  • 嵌套子查询
  • SQL Server子查询类型
    • SQL Server子查询用于代替表达式
    • SQL Server子查询与IN运算符一起使用
    • SQL Server子查询与ANY运算符一起使用
    • SQL Server子查询与ALL运算符一起使用
    • SQL Server子查询与EXISTS或NOT EXISTS运算符一起使用
    • SQL Server子查询在FROM子句中使用
  • SQL Server关联子查询
    • 简介
  • SQL Server EXISTS
    • 简介
    • 示例
      • A）EXISTS与子查询返回NULL的示例
      • B）EXISTS与关联子查询的示例
      • B）EXISTS与IN对比的示例
  • SQL Server ANY
    • 简介
    • 示例
  • SQL Server ALL
    • 简介
    • 示例
      • 1）scalar_expression > ALL (子查询)
      • 1）scalar_expression < ALL (子查询)

简介

子查询是嵌套在另一条语句（如SELECT、INSERT、UPDATE或DELETE）中的查询。

看如下例子

有如下订单和顾客表

以下语句显示如何通过WHERE子句中的子查询查找位于纽约的客户的销售订单：

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'

注意，必须将整个子查询写在括号()内

外层的查询称为外部查询(outer query),内层的子查询也叫内部查询(inner query)，下图展示了他们的结构：

SQL Server执行上述整个查询示例过程，如下所示：

首先，它执行子查询以获取位于纽约的客户的客户标识号列表。

其次，SQL Server将子查询返回的客户标识号用于in运算符，并执行外部查询以获得最终结果集。

嵌套子查询

子查询可以嵌套在另一个子查询中。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 = 'Strider'
                OR brand_name = 'Trek'
            )
    )
ORDER BY
    list_price;

首先，SQL Server执行以下子查询以获取Strider和Trek品牌的品牌标识号列表：

SELECT
    brand_id
FROM
    production.brands
WHERE
    brand_name = 'Strider'
OR brand_name = 'Trek';

其次，SQL Server计算属于这些品牌的所有产品的平均价格列表。

SELECT
    AVG (list_price)
FROM
    production.products
WHERE
    brand_id IN (6,9)

第三，SQL Server查找其标价高于Strider或Trek品牌所有产品的平均标价的产品。

SQL Server子查询类型

可以在许多地方使用子查询：

• 用于代替表达式
• 与IN和NOT IN一起使用
• 与ANY和ALL一起使用
• 与EXISTS和NOT EXISTS一起使用
• 在UPDATE, DELETE, 或INSERT语句中
• 在FROM子句中

SQL Server子查询用于代替表达式

如果子查询返回单个值，则可以在使用表达式的任何位置使用该值。

在以下示例中，子查询用作SELECT语句中名为max_list_price的列表达式。

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;

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'
    );

这个查询包含两步：

• 1.首先，内部查询返回与山地自行车和公路自行车名称匹配的类别id列表。
• 2.其次，这些id被替换到外部查询中，该查询将查找类别标识号与列表中的一个值匹配的产品名称。

SQL Server子查询与ANY运算符一起使用

语法：

表达式 比较运算符 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运算符一起使用

语法：

表达式 比较运算符 ANY (子查询)

其中，子查询返回值列表，只有值列表中的所有值都满足条件，才返回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
    )

SQL Server子查询与EXISTS或NOT EXISTS运算符一起使用

语法：

WHERE [NOT] EXISTS (subquery)

如果子查询有返回结果(注意，只要有返回结果就行，不管返回什么)，条件就返回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;

把EXISTS替换成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;

SQL Server子查询在FROM子句中使用

如果想查找到所有销售人员订单总数的平均值。可以先找到每个销售人员的订单数量：

SELECT 
   staff_id, 
   COUNT(order_id) order_count
FROM 
   sales.orders
GROUP BY 
   staff_id;

如下结果便是销售人员及各自的销售订单数

然后，由于上述查询返回的结果集看起来像虚拟表，所以可以将上面整个查询放在另一个查询的FROM子句中，再使用AVG()求平均值，如下所示：

SELECT 
   AVG(order_count) average_order_count_by_staff
FROM
(
    SELECT 
	staff_id, 
        COUNT(order_id) order_count
    FROM 
	sales.orders
    GROUP BY 
	staff_id
) t;

在FROM子句中放置的查询必须具有表别名。

在本例中，我们使用t作为子查询的表别名。
为了得到最终结果，SQL Server执行以下步骤：

• 在FROM子句中执行子查询。
• 把查询的结果看作一张虚拟表来执行外部查询。

SQL Server关联子查询

简介

关联子查询是使用外部查询的值的子查询。换句话说，相关子查询的值取决于外部查询。

由于子查询依赖外部查询的值，所以相关子查询不能作为简单的单独子查询独立执行。

此外，相关子查询会被重复执行，对于外部查询计算的每一行数据执行一次子查询。相关子查询也称为重复子查询。

有如下产品表：

查找同一类别中，标价最高的那些产品

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;

此示例中，对于通过外部查询评估的每个产品，该子查询在其类别中找到了所有产品的最高价格。

如果当前产品的价格等于其类别中所有产品的最高价格，则该产品将包含在结果集中。下一个产品继续此过程，以此类推。

子查询中的如下子句使用了外部查询的p1.category_id的值：

p2.category_id = p1.category_id

显而易见，对于外部查询在评估每个产品是否符合WHERE条件时，关联子查询都会执行一次。

SQL Server EXISTS

简介

EXISTS是一个逻辑运算符，可以检查子查询是否返回任何行。如果子查询返回一行或多行，则EXISTS运算符返回TRUE。

语法：

EXISTS (subquery)

语法中，子查询是一个SELECT语句。一旦子查询返回行，EXISTS操作符将返回TRUE并立即停止处理。

注意，即使子查询返回NULL，EXISTS操作符的计算结果仍然为TRUE。

示例

A）EXISTS与子查询返回NULL的示例

有如下顾客表

以下示例返回customers表中的所有行：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    EXISTS (SELECT NULL)
ORDER BY
    first_name,
    last_name;

在本例中，子查询返回了一个包含NULL的结果集，该结果集导致EXISTS运算符的计算结果为TRUE。因此，整个查询返回customers表中的所有行。

B）EXISTS与关联子查询的示例

有如下订单和客户表

以下示例查找下了两个以上订单的所有客户：

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;

在本例中，我们有一个关联子查询，返回下了两个以上订单的客户。

如果客户下的订单数量小于或等于2，则子查询将返回一个空结果集，导致EXISTS运算符的计算结果为FALSE。

B）EXISTS与IN对比的示例

以下语句使用IN运算符查找来自圣何塞的客户订单：

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运算符：

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;

SQL Server ANY

简介

ANY是一个逻辑运算符，它将值与子查询返回的一组单列值进行比较。

语法：

scalar_expression comparison_operator ANY (subquery)

在这个语法中：

• scalar_expression是一个标量表达式
• comparison_operator是任何有效的比较运算符，包括相等（=）、不相等（＜>）、大于（＞）、大于或等于（＞=）、小于（＜）、小于或等于（＜=）。
• subquery是一个SELECT语句，它返回单个列的结果集，其中数据与标量表达式的数据类型相同。

如果子查询返回v1、v2、…、vn的值列表。如果存在比较（scalar_expression，vi）返回TRUE，则ANY运算符返回TRUE。否则，返回FALSE。

注意，SOME与ANY操作符等价。

示例

有如下产品表

查找在销售订单中以数量两个以上销售的产品：

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;

SQL Server ALL

简介

SQL Server ALL是一个逻辑运算符，用于将标量值与子查询返回的值列表进行比较。

语法：

scalar_expression comparison_operator ALL (subquery) 

其中：

• scalar_expression是一个标量表达式。
• comparison_operator是任何有效的比较运算符，包括相等（=）、不相等（＜>）、大于（＞）、大于或等于（＞=）、小于（＜）、小于或等于（＜=）。
• 括号内的子查询是一个SELECT语句，它返回单个列的结果。此外，返回列的数据类型必须与标量表达式的数据类型相同。

如果子查询返回的所有值都满足条件，才返回TRUE，否则返回FALSE

示例

有如下产品表

下面返回每种品牌产品的平均价格：

SELECT
    AVG (list_price) avg_list_price
FROM
    production.products
GROUP BY
    brand_id
ORDER BY
    avg_list_price;

1）scalar_expression > ALL (子查询)

如果scalar_expression大于子查询返回的所有值，即大于子查询返回的最大值，则表达式返回TRUE。

例如，查找标价大于所有品牌的产品平均标价的产品：

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;

1）scalar_expression < ALL (子查询)

如果scalar_expression小于子查询返回的所有值，即小于子查询返回的最小值，则表达式返回TRUE。

查找标价小于所有品牌的产品平均标价的产品：

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运算符与以下比较运算符之一一起使用，例如等于（=）、大于或等于（>=）、小于或等于（<=）和不等于（<>）。