在 SELECT 查询中使用子查询

  子查询(或称嵌套查询)是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。子查询中还可以嵌套子查询,最多嵌套32层。

  任何允许使用表达式的地方都可以使用子查询。

  子查询也称为内部查询(或内部SELECT),而包含子查询的语句也称为外部查询(或外部SELECT)。子查询的结果集被用于外部查询。


一、使用子查询的场景

  可以在许多位置指定子查询,以下是常见的情形:

1. 使用别名的子查询

  子查询和外部查询可以引用同一表,为了使查询语句清晰易懂,子查询应使用显式别名。

SELECT e1.StateProvinceID, e1.AddressID

FROM Address AS e1

WHERE e1.AddressID IN

  ( SELECT e2.AddressID

    FROM Address AS e2

    WHERE e2.StateProvinceID = 39 )


  大部分的子查询都可以改写成联接。例如,上例可以改为自联接。

SELECT e1.StateProvinceID, e1.AddressID

FROM Address AS e1

 INNER JOIN Address AS e2

   ON e1.AddressID = e2.AddressID

   AND e2.StateProvinceID = 39;


2. 使用 IN 或 NOT IN 的子查询

  此类子查询将在外部查询中排除某些子集。

SELECT Name

FROM Product

WHERE ProductSubcategoryID NOT IN

  ( SELECT ProductSubcategoryID

    FROM ProductSubcategory

    WHERE Name = 'Mountain Bikes' OR Name = 'Road Bikes' )


3. 使用比较运算符的子查询

  子查询可以由一个比较运算符(=、< >、>、> =、<、! >、! < 或 < =)引入。

SELECT CustomerID

FROM Customer

WHERE TerritoryID =

  ( SELECT TerritoryID

    FROM SalesPerson

    WHERE BusinessEntityID = 276 )


  在子查询中还可以使用分组和聚集函数。例如:

SELECT Name

FROM Product

WHERE ListPrice >

  ( SELECT MIN (ListPrice)

    FROM Product

    GROUP BY ProductSubcategoryID

    HAVING ProductSubcategoryID = 14 )


4. 使用 ANY、SOME 或 ALL 的子查询

  可以用 ALL 或 ANY 关键字修改引入子查询的比较运算符。SOME 是与 ANY 等效的 ISO 标准。

通过修改的比较运算符引入的子查询返回零个值或多个值的列表,并且可以包括 GROUP BY 或 HAVING 子句。这些子查询可以用 EXISTS 重新表述。

SELECT Name

FROM Product

WHERE ProductSubcategoryID =ANY

   (SELECT ProductSubcategoryID

    FROM ProductSubcategory

    WHERE Name = 'Wheels') ;


5. 使用 EXISTS 或 NOT EXISTS 的子查询

  使用 EXISTS 关键字引入子查询后,子查询的作用就相当于进行存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据,它只返回 TRUE 或 FALSE 值。

SELECT Name

FROM Product

WHERE EXISTS

  ( SELECT *

    FROM ProductSubcategory

    WHERE ProductSubcategoryID =

           ProductSubcategoryID

       AND Name = 'Wheels' )


6. 替代表达式的子查询

  在 T-SQL 中,除了在 ORDER BY 列表中以外,在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何能够使用表达式的地方都可以用子查询替代。

SELECT Name, ListPrice,

(SELECT AVG(ListPrice) FROM Product) AS Average,

   ListPrice - (SELECT AVG(ListPrice) FROM Product)

   AS Difference

FROM Product

WHERE ProductSubcategoryID = 1;



二、子查询的类型

1. 自包含子查询(Self-contained subquery)

  在包含自包含子查询的查询中,自包含子查询是独立于其所隶属的外部查询的,因此自包含查询是可以独立运行的,此类子查询也称为独立子查询。


2. 相关子查询(Correlated subquery)

  在包含相关子查询(也称为重复子查询)的查询中,子查询依靠外部查询获得值。

  在下例中,外部查询逐个选择 SalesOrderDetail(即 s1)的行。子查询为外部查询中的选择计算正在考虑的每个销售的平均数量。对于每个可能的 s1 值,如果数量小于计算的平均值,SQL Server 将计算子查询并将所考虑的记录放入结果中。

SELECT ProductID, OrderQty

FROM SalesOrderDetail s1

WHERE s1.OrderQty <

   (SELECT AVG (s2.OrderQty)

    FROM SalesOrderDetail s2

    WHERE s2.ProductID = s1.ProductID)



三、子查询的常见问题

1. 注意区分标量(Scalar)子查询与多值(Multi-valued)子查询

  标量子查询仅返回单个的值,而多值子查询返回多个值。

  在下例中,如果子查询返回单个值,则运行此查询时不会发生错误。如果子查询返回多个值,则查询将失败。

SELECT e1.StateProvinceID, e1.AddressID

FROM Address AS e1

WHERE e1.AddressID =

  ( SELECT e2.AddressID

    FROM Address AS e2

    WHERE e2.StateProvinceID = 39 )

相关文章
相关标签/搜索
每日一句
    每一个你不满意的现在,都有一个你没有努力的曾经。
公众号推荐
   一个历史类的公众号,欢迎关注
一两拨千金