SQL 之 子查询

关键字：EXISTS

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

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

三种方式：

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

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

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

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

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

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

SELECT
CustomerName AS 'Customer Name'
ISNULL (CashOrders.SumOfOrders, 0) AS 'Total Cash Orders'
FROM Customers
LEFT JOIN

(SELECT
	CustomerID,
	SUM(OrderAmount) as 'SumOfOrders'
	FROM Orders
	WHERE OrderType = 'Cash'
	GROUP BY CustomerID) AS CashOrders

ON Customers.CustomerID = CashOrders.CustomerID
ORDER BY Customers.CustomerID

子查询：

        SELECT
	CustomerID,
	SUM(OrderAmount) as 'SumOfOrders'
	FROM Orders
	WHERE OrderType = 'Cash'
	GROUP BY CustomerID

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

SELECT
CustomerName AS 'Customer Name'
ISNULL (CashOrders.SumOfOrders, 0) AS 'Total Cash Orders'
FROM Customers
LEFT JOIN

(Subquery) AS CashOrders

ON Customers.CustomerID = CashOrders.CustomerID
ORDER BY Customers.CustomerID

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

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

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

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

WHERE State IN 
(SELECT 
States
FROM StateTable
WHERE Region 'Midwest')

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

另一个示例：

SELECT CustomerName AS 'Customer Name'
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders
WHERE OrderType = 'Cash')

3. 关联子查询

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

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

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

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

SELECT
CustomerName AS 'Customer Name'
FROM Customers
WHERE
(SELECT
SUM(OrderAmount)
FROM Orders
WHERE 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 Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, CustomerName
HAVING SUM(OrderAmount) < 20

4. EXISTS关键字

查询哪些客户下了订单：

SELECT
CustomerName AS 'Customer Name'
FROM Customers
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID)

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

IN

SELECT
CustomerName AS 'Customer Name'
FROM Customers
WHERE CustomerID IN
(SELECT Orders.CustomerID
FROM Orders)

INNER JOIN

SELECT
CustomerName AS 'Customer Name'
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerName

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

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

SELECT
CustomerName AS 'Customer Name',
COUNT(OrderID) AS 'Number of Orders'
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, CustomerName
ORDER BY Customers.CustomerID

使用子查询：

SELECT
CustomerName AS 'Customer Name',
(SELECT 
COUNT(OrderID)
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID) AS 'Number of Orders'
FROM Customers
ORDER BY Customers.CustomerID

主查询的一般格式：

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