SqlServer子查询类型

最新推荐文章于 2024-08-26 11:17:37 发布

.NET跨平台

最新推荐文章于 2024-08-26 11:17:37 发布

阅读量801

点赞数

分类专栏： SqlServer数据库文章标签： sql server

SqlServer数据库专栏收录该内容

180 篇文章 33 订阅

订阅专栏

可以在许多位置指定子查询：

使用别名。有关详细信息，请参阅使用别名的子查询。
使用 IN 或 NOT IN。有关详细信息，请参阅使用 IN 的子查询和使用 NOT IN 的子查询。
在 UPDATE、DELETE 和 INSERT 语句中。有关详细信息，请参阅 UPDATE、DELETE 和 INSERT 语句中的子查询。
使用比较运算符。有关详细信息，请参阅使用比较运算符的子查询。
使用 ANY、SOME 或 ALL。有关详细信息，请参阅用 ANY、SOME 或 ALL 修改的比较运算符。
使用 EXISTS 或 NOT EXISTS。有关详细信息，请参阅使用 EXISTS 的子查询和使用 NOT EXISTS 的子查询。
代替表达式。有关详细信息，请参阅用于替代表达式的子查询。

使用别名的子查询

许多其中的子查询和外部查询引用同一表的语句可称为自联接（将某个表与自身联接）。例如，可以使用子查询查找与 Terri Duffy 具有相同经理的雇员：

USE AdventureWorks;
GO
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IN
    (SELECT ManagerID
     FROM HumanResources.Employee
     WHERE EmployeeID = 12)

下面是结果集：

EmployeeID  ManagerID
----------- -----------
6           109
12          109
21          109
42          109
140         109
148         109
273         109

(7 row(s) affected)

也可以使用自联接：

USE AdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
INNER JOIN HumanResources.Employee AS e2
ON e1.ManagerID = e2.ManagerID
AND e2.EmployeeID = 12

由于自联接的表会以两种不同的角色出现，因此必须有表别名。别名也可用于在内部查询和外部查询中引用同一表的嵌套查询。

USE AdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
WHERE e1.ManagerID IN
    (SELECT e2.ManagerID
     FROM HumanResources.Employee AS e2
     WHERE e2.EmployeeID = 12)

显式别名清楚地表明，在子查询中对 HumanResources.Employee 的引用并不等同于在外部查询中的该引用。

使用 IN 的子查询

通过 IN（或 NOT IN）引入的子查询结果是包含零个值或多个值的列表。子查询返回结果之后，外部查询将利用这些结果。

下面的查询查找 Adventure Works Cycles 生成的所有车轮产品的名称。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

下面是结果：

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

该语句分两步进行评估。首先，内部查询返回与名称 'Wheel' (17) 匹配的子类别标识号。然后，这些值将替换到外部查询中，此外部查询将在 Product 中查找与子类别标识号匹配的产品名称。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID in ('17')

使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表中的列。例如，如果要在结果中包括产品子类别的名称，则必须使用联接版本。

Use AdventureWorks;
GO
SELECT p.Name, s.Name
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.Name = 'Wheels'

下面是结果：

Name                                               Name
LL Mountain Front Wheel                            Wheels
ML Mountain Front Wheel                            Wheels
HL Mountain Front Wheel                            Wheels
LL Road Front Wheel                                Wheels
ML Road Front Wheel                                Wheels
HL Road Front Wheel                                Wheels
Touring Front Wheel                                Wheels
LL Mountain Rear Wheel                             Wheels
ML Mountain Rear Wheel                             Wheels
HL Mountain Rear Wheel                             Wheels
LL Road Rear Wheel                                 Wheels
ML Road Rear Wheel                                 Wheels
HL Road Rear Wheel                                 Wheels
Touring Rear Wheel                                 Wheels

(14 row(s) affected)

以下示例查找所有信誉度良好，与 Adventure Works Cycles 有过至少 20 项定购记录，并且其平均交付时间小于 16 天的所有供应商的名称。

Use AdventureWorks;
GO
SELECT Name
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND VendorID IN
    (SELECT VendorID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16)

下面是结果：

Name
--------------------------------------------------
Electronic Bike Repair & Supplies
Comfort Road Bicycles
Compete, Inc.
Compete Enterprises, Inc
First Rate Bicycles
First National Sport Co.
Competition Bike Training Systems
Circuit Cycles
Crowley Sport
Expert Bike Co

(10 row(s) affected)

评估内部查询后，产生符合子查询限定条件的供应商的 ID 号。然后评估外部查询。注意，在内部和外部查询的 WHERE 子句中，都可以包括多个条件。

使用联接，同一查询可以用如下方式表示：

USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.VendorID = p.VendorID
WHERE CreditRating = 1
AND MinOrderQty >= 20
AND AverageLeadTime < 16

联接总是可以表示为子查询。子查询经常（但不总是）可以表示为联接。这是因为联接是对称的：无论以何种顺序联接表 A 和 B，都将得到相同的结果。而对子查询来说，情况则并非如此。

使用 NOT IN 的子查询

通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。

以下查询将查找不是成品自行车的产品名称。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Mountain Bikes' 
        OR Name = 'Road Bikes'
        OR Name = 'Touring Bikes')

此语句无法转换为一个联接。这种类似但不相等连接有不同的含义：它在某个非成品自行车的子类别中查找产品名称。有关解释不基于相等的联接的含义的详细信息，请参阅联接三个或更多表。

UPDATE、DELETE 和 INSERT 语句中的子查询

可以在 UPDATE、DELETE、INSERT 和 SELECT 数据操作 (DML) 语句中嵌套子查询。

以下示例使 Production.Product 表的 ListPrice 列中的值加倍。WHERE 子句中的子查询将引用 Purchasing.ProductVendor 表以便将 Product 表中更新的行仅限制为 VendorID 51 对应的那些行。

USE AdventureWorks;
GO 
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID 
     FROM Purchasing.ProductVendor
     WHERE VendorID = 51);
GO

下面是一条使用联接的等效 UPDATE 语句：

USE AdventureWorks;
GO 
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND pv.VendorID = 51;
GO

使用比较运算符的子查询

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

与使用 IN 引入的子查询一样，由未修改的比较运算符（即后面不接 ANY 或 ALL 的比较运算符）引入的子查询必须返回单个值而不是值列表。如果这样的子查询返回多个值，Microsoft SQL Server 2005 将显示一条错误信息。

要使用由未修改的比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。

例如，如果假定每个销售员只负责一块销售区域，而您要找出 Linda Mitchell 所负责区域的客户，那么您可以编写一条语句，带上简单的 = 比较运算符引入的子查询。

USE AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE SalesPersonID = 276)

但是，如果 Linda Mitchell 负责的销售区域不止一个，则会产生一条错误信息。这时可以用 IN 表达式（= ANY 也可以）来代替 = 比较运算符。

由未修改的比较运算符引入的子查询经常包括聚合函数，因为这些子查询要返回单个值。例如，下面的语句将找出定价高于平均定价的所有产品的名称。

Use AdventureWorks
SELECT Name
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product)

因为由未修改的比较运算符引入的子查询必须返回单个值，所以除非知道 GROUP BY 或 HAVING 子句本身会返回单个值，否则不能包括 GROUP BY 或 HAVING 子句。例如，下面的查询将找出子类别 14 中定价高于最低定价的产品。

Use AdventureWorks
SELECT Name
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14)

用 ANY、SOME 或 ALL 修改的比较运算符

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

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

以 > 比较运算符为例，>ALL 表示大于每一个值。换句话说，它表示大于最大值。例如，>ALL (1, 2, 3) 表示大于 3。>ANY 表示至少大于一个值，即大于最小值。因此 >ANY (1, 2, 3) 表示大于 1。

若要使带有 >ALL 的子查询中的行满足外部查询中指定的条件，引入子查询的列中的值必须大于子查询返回的值列表中的每个值。

同样，>ANY 表示要使某一行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于子查询返回的值列表中的一个值。

下面的查询提供一个由 ANY 修改的比较运算符引入的子查询的示例。它查找定价高于或等于任何产品子类别的最高定价的产品。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID)

对于每个产品子类别，内部查询查找最高定价。外部查询查看所有这些值，并确定定价高于或等于任何产品子类别的最高定价的单个产品。如果 ANY 更改为 ALL，查询将只返回定价高于或等于内部查询返回的所有定价的那些产品。

如果子查询不返回任何值，那么整个查询将不会返回任何值。

=ANY 运算符与 IN 等效。例如，若要查找 Adventure Works Cycles 生产的所有轮子产品的名称，可以使用 IN 或 =ANY。

--Using =ANY
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

--Using IN
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

下面是任一查询的结果集：

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

但是，< >ANY 运算符则不同于 NOT IN：< >ANY 表示不等于 a，或者不等于 b，或者不等于 c。NOT IN 表示不等于 a、不等于 b 并且不等于 c。<>ALL 与 NOT IN 表示的意思相同。

例如，以下查询查找位于任何销售人员都不负责的地区的客户。

Use AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson)

结果包含除销售地区为 NULL 的客户以外的所有客户，因为分配给客户的每个地区都由一个销售人员负责。内部查询查找销售人员负责的所有销售地区，然后对于每个地区，外部查询查找不在任一地区的客户。

由于同一原因，当在此查询中使用 NOT IN 时，结果将不包含任何客户。

还可以使用 < >ALL 运算符获得相同的结果，该运算符与 NOT IN 等效。

使用 EXISTS 的子查询

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

使用 EXISTS 引入的子查询的语法如下：

WHERE [NOT] EXISTS (subquery)

以下查询将查找 Wheels 子类别中所有产品的名称：

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE EXISTS
    (SELECT * 
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID = 
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels')

下面是结果集：

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

若要了解此查询的结果，请依次考虑每件产品的名称。此值是否使子查询至少返回一行？换句话说，查询是否使存在测试的计算结果为 TRUE？

注意，使用 EXISTS 引入的子查询在下列方面与其他子查询略有不同：

EXISTS 关键字前面没有列名、常量或其他表达式。
由 EXISTS 引入的子查询的选择列表通常几乎都是由星号 (*) 组成。由于只是测试是否存在符合子查询中指定条件的行，因此不必列出列名。

由于通常没有备选的、非子查询的表示法，因此 EXISTS 关键字很重要。尽管一些使用 EXISTS 创建的查询不能以任何其他方法表示，但许多查询都可以使用 IN 或者由 ANY 或 ALL 修改的比较运算符来获取类似结果。

例如，可以使用 IN 表示上述查询：

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

使用 NOT EXISTS 的子查询

NOT EXISTS 与 EXISTS 的工作方式类似，只是如果子查询不返回行，那么使用 NOT EXISTS 的 WHERE 子句会得到令人满意的结果。

例如，查找不在轮子类别中的产品的名称：

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE NOT EXISTS
    (SELECT * 
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID = 
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels')

用于替代表达式的子查询

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

以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

USE AdventureWorks;
GO
SELECT Name, ListPrice, 
(SELECT AVG(ListPrice) FROM Production.Product) AS Average, 
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1