Sql Server系列:子查询

时间:2022-01-26 03:28:26

1 子查询概念

  子查询是嵌套在另一个查询中的普通T-SQL查询。在有一个SELECT语句通过使用小括号创建子查询,作为另一个查询的部分数据或条件的基础。

  子查询通常用于满足以下某个需求:

  ◊ 将一个查询分解为一系列的逻辑步骤

  ◊ 提供一个列表作为WHERE子句或[IN | EXISTS | ANY |ALL]的目标

  ◊ 为父查询中的每个记录提供一个查询表

  还值得注意的是,大部分(不是全部)子查询可以使用连接来编写。

2 构建嵌套子查询

  嵌套子查询只在一个方向嵌套:返回在外部查询中使用的单个值,或者与IN运算符一起使用的一个完整的值列表

  子查询的查询语法类似以下两个语法模板之一:

SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> = (
SELECT <single column>
FROM <SomeTable>
WHERE <condition that results in only row returned>
)
SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> IN (
SELECT <single column>
FROM <SomeTable>
WHERE <condition>
)

2.1 使用返回单个值的SELECT语句的嵌套查询

  查询UnitPrice大于等于10的CategoryName

SELECT DISTINCT [CategoryName]
FROM [dbo].[Category] c
INNER JOIN [dbo].[Product] p
ON c.[CategoryID] = p.[CategoryID]
WHERE p.[UnitPrice] >= 10

  查询UnitPrice最小的记录

SELECT p.[ProductID], p.[ProductCode], p.[ProductName], c.[CategoryName], p.[UnitPrice]
FROM [dbo].[Category] c
INNER JOIN [dbo].[Product] p
ON c.[CategoryID] = p.[CategoryID]
WHERE p.[UnitPrice] = (
SELECT MIN([UnitPrice]) FROM [dbo].[Product]
)

2.2 使用返回多个值的子查询的嵌套查询

SELECT [dbo].[Product].* FROM [dbo].[Product]
WHERE [CategoryID] IN (
SELECT [CategoryID] FROM [dbo].[Category] WHERE [CategoryID] <= 10
)

  尽管使用IN的嵌套查询方式可以实现,但更好的方式是使用内连接实现这种查询,而不是使用使用嵌套的SELECT。

  上例的子查询使用INNER JOIN方式:

SELECT [dbo].[Product].* FROM [dbo].[Product]
INNER JOIN [dbo].[Category]
ON [dbo].[Product].[CategoryID] = [dbo].[Category].[CategoryID]
WHERE [dbo].[Category].[CategoryID] <= 10

  出于性能方面的考虑,如果没有特别的理由要使用嵌套的SELECT,则还是应使用连接方式作为默认的解决方案。在大部分情况下,SQL Server会将嵌套子查询解决方案解析为和使用连接用于的查询计划,在检查嵌套子查询和内连接查询计划时,会发现它们的完全相同的计划。大多数情况下,这两种方法没有多大的区别。当查询计划不同时,连接通常的更好的选择。

3 关联子查询

  关联子查询与嵌套子查询的不同之处在于信息传递是双向的,而不是单向的。在嵌套子查询中,内部查询只处理一次,然后将信息传递到外部查询,而外部查询也只执行一次。

  在关联子查询中,内部查询用外部查询提供的信息运行,外部查询使用内部查询的信息运行。

  关联查询分3个步骤进行的处理过程:

  ◊ 外部查询获得一个记录,将记录传递到内部查询;

  ◊ 内部查询根据传递的值执行;

  ◊ 内部查询将结果值返回外部查询,外部查询再利用这些值完成处理过程。

3.1 在WHERE子句中的关联子查询

  示例:查询在Product中每个CategoryID中UnitPrice最贵的Product记录

  实现方式一:使用两个单独的查询,先创建一个临时表,再进行连接。

-- 查询每个CategoryID中UnitPrice最大的记录
SELECT [CategoryID], MAX([UnitPrice]) AS [UnitPrice]
INTO #MaxUnitPriceProduct
FROM [dbo].[Product]
GROUP BY [CategoryID]

-- SELECT * FROM #MaxUnitPriceProduct
--
查询每个CategoryID中UnitPrice最大的Product记录
SELECT p.[ProductID], p.[CategoryID], p.[ProductCode], p.[ProductName], p.[UnitPrice]
FROM [dbo].[Product] p
INNER JOIN #MaxUnitPriceProduct t
ON p.[CategoryID] = t.[CategoryID]
AND p.[UnitPrice] = t.[UnitPrice]

DROP TABLE #MaxUnitPriceProduct

  实现方式二:使用关联子查询

SELECT p1.[ProductID], p1.[CategoryID], p1.[ProductCode], p1.[ProductName], p1.[UnitPrice]
FROM [dbo].[Product] p1
WHERE P1.[UnitPrice] = (
SELECT MAX(p2.[UnitPrice]) FROM [dbo].[Product] p2
WHERE p2.[CategoryID] = p1.[CategoryID]
)

3.2 在SELECT列表中的关联子查询

SELECT p1.[ProductName], (
SELECT MAX([UnitPrice]) FROM [dbo].[Product] P2
WHERE P2.[CategoryID] = p1.[CategoryID]
)
AS [UnitPrice]
FROM [dbo].[Product] P1

5 派生表

  派生表是有一个查询结果集的行和列构成,要创建派生表,需要做以下两件事情:

  ◊ 将生成结果集的查询用小括号括起来

  ◊ 给查询结果取别名,这样可以作为表被引用

  语法结构

SELECT <select list>
FROM (<query that returns a regular resultset>) AS <alias name>
JOIN <some other base or derived table>

  示例

SELECT p.[ProductID],p.[ProductCode],p.[ProductName],p.[UnitPrice],t.[CategoryName]
FROM [dbo].[Product] p
INNER JOIN (
SELECT [CategoryID],[CategoryName] FROM [dbo].[Category]
WHERE [CategoryID] <= 10
) t
ON p.[CategoryID] = t.[CategoryID]