SQL Server子查询示例

在本教程中，将了解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子查询类型

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

• 代替表达
• 使用IN或NOT IN
• ANY或ALL
• 有EXISTS或NOT EXISTS语句中。
• 在UPDATE，DELETE或INSERT语句中。

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;

执行上面查询语句，得到以下结果：