SQL 之 子查询

关键字：EXISTS

子查询：插入到SELECT语句中的另一条SELECT语句。

子查询可以插入到SELECT语句的任何子句中，方式取决于子查询是columnlist、tablelist还是condition

三种方式：

（1）当子查询是tablelist的一部分时，它指定了一个数据源

（2）当子查询是columnlist的一部分时，它创建了一个单个的计算的列；

（3）当子查询是condition的一部分时，它成为查询条件的一部分；

1. 使用子查询作为数据源

把子查询作为FROM子句的一部分时，它立即创建了一个新的数据源，只是不会保存而已。

示例：如果想要看到客户的列表，以及它们所下的先进订单的总金额

SELECTCustomerName AS 'Customer Name'ISNULL (CashOrders.SumOfOrders, 0) AS 'Total Cash Orders'FROM CustomersLEFT JOIN(SELECTCustomerID,SUM(OrderAmount) as 'SumOfOrders'FROM OrdersWHERE OrderType = 'Cash'GROUP BY CustomerID) AS CashOrdersON Customers.CustomerID = CashOrders.CustomerIDORDER BY Customers.CustomerID

子查询：

        SELECTCustomerID,SUM(OrderAmount) as 'SumOfOrders'FROM OrdersWHERE OrderType = 'Cash'GROUP BY CustomerID

子查询限制了我们只能选择现金订单，并且结构依据CustomerID分组（也就是每个客户），同时有一列计算的列，求每组订单的总和。
主查询的结构：

SELECTCustomerName AS 'Customer Name'ISNULL (CashOrders.SumOfOrders, 0) AS 'Total Cash Orders'FROM CustomersLEFT JOIN(Subquery) AS CashOrdersON Customers.CustomerID = CashOrders.CustomerIDORDER BY Customers.CustomerID

给子查询赋了一个表的别名CashOrders，允许在主查询的SELECT语句中引用子查询中的列：ISNULL (CashOrders.SumOfOrders, 0) AS 'Total Cash Orders'

2. 在查询条件中使用子查询

简单的条件：WHERE State IN ('IL', 'NY');

IN里面只列举了若干个直接量，如果是更复杂更多的可选值：

WHERE State IN (SELECT StatesFROM StateTableWHERE Region 'Midwest')

只要符合是中西部的州都匹配

另一个示例：

SELECT CustomerName AS 'Customer Name'FROM CustomersWHERE CustomerID IN(SELECT CustomerIDFROM OrdersWHERE OrderType = 'Cash')

3. 关联子查询

关联与非关联：子查询是否与包含它的查询相关联。

非关联：子查询完全独立于外围的查询语句。非关联查询作为整个SELECT语句的一部分，只会计算一次。

关联：与外围查询进行关联。关联子查询需要针对返回的每一行逐行计算，并且可能在每次执行子查询时得到不同的结果。

创建订单总金额少于20的客户列表：

SELECTCustomerName AS 'Customer Name'FROM CustomersWHERE(SELECTSUM(OrderAmount)FROM OrdersWHERE Customers.CustomerID = Orders.CustomerID)< 20

子查询：

SELECT
SUM(OrderAmount)
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID

为什么是关联的？因为单独执行子查询会出错，子查询的上下文没有Customers.CustomerID列

主查询结构：

SELECT
CustomerName AS 'Customer Name'
FROM Customers
WHERE
(Subquery) < 20

PS：只有返回单独的值，这类的子查询才会工作。（比如返回SUM(OrderAmount)）。作为一个关联子查询，子查询需要针对外围的查询对每一位客户进行计算。

等价查询：

不用子查询，可以用分组GROUP BY关键字（GROUP BY Customers.CustomerID, CustomerName）和HAVING关键字（查询条件 HAVING SUM(OrderAmount) < 20）

SELECT CustomerName AS 'Customer Name'FROM CustomersLEFT JOIN OrdersON Customers.CustomerID = Orders.CustomerIDGROUP BY Customers.CustomerID, CustomerNameHAVING SUM(OrderAmount) < 20

4. EXISTS关键字

查询哪些客户下了订单：

SELECTCustomerName AS 'Customer Name'FROM CustomersWHERE EXISTS(SELECT *FROM OrdersWHERE Customers.CustomerID = Orders.CustomerID)

 EXISTS关键字是这样的：只要子查询返回了任意数据，EXISTS的计算结果就为真，所以子查询选取了所有的列（*），因为它不关心在子查询中选中哪些特定的数据，只关心确定子查询中是否存在任何数据。

IN

SELECTCustomerName AS 'Customer Name'FROM CustomersWHERE CustomerID IN(SELECT Orders.CustomerIDFROM Orders)

INNER JOIN

SELECTCustomerName AS 'Customer Name'FROM CustomersINNER JOIN OrdersON Customers.CustomerID = Orders.CustomerIDGROUP BY Customers.CustomerName

5. 使用子查询作为计算的列

示例：希望看到一列客户以及他们所下订单的数量

SELECTCustomerName AS 'Customer Name',COUNT(OrderID) AS 'Number of Orders'FROM CustomersLEFT JOIN OrdersON Customers.CustomerID = Orders.CustomerIDGROUP BY Customers.CustomerID, CustomerNameORDER BY Customers.CustomerID

使用子查询：

SELECTCustomerName AS 'Customer Name',(SELECT COUNT(OrderID)FROM OrdersWHERE Customers.CustomerID = Orders.CustomerID) AS 'Number of Orders'FROM CustomersORDER BY Customers.CustomerID

主查询的一般格式：

SELECT
CustomerName AS 'Customer Name',
(SubqueryResult) AS 'Number of Orders'
FROM Customers
ORDER BY Customers.CustomerID