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;

執行上面查詢語句，得到以下結果：