子查詢

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

子查詢長什麼樣？

子查詢

子查詢長什麼樣？

含外層查詢的子查詢

FROM 子查詢

用在 FROM 的子查詢

FROM 子查詢

SELECT OrderID, 
       CustomerID, 
       NumDays
FROM 
    (SELECT *, 
     DATEDIFF(DAY,OrderDate,ShippedDate) AS NumDays 
     FROM Orders) AS o
WHERE NumDays >= 35;

OrderID	CustomerID	NumDays
10380	HUNGO	35
10427	PICCO	35
10545	LAZYK	35
10593	LEHMS	35
10660	HUNGC	37
10777	GOURL	37
10924	BERGS	35

WHERE 子查詢

用在 WHERE 的子查詢

WHERE 子查詢

SELECT CustomerID
       ,CompanyName 
FROM Customers
WHERE CustomerID 
         IN (SELECT CustomerID 
         FROM Orders 
         WHERE Freight > 800);

CustomerID	CompanyName
QUEEN	Queen Cozinha
QUICK	QUICK-Stop
SAVEA	Save-a-lot Markets

SELECT 子查詢

用在 SELECT 的子查詢

SELECT 子查詢

SELECT CustomerID, 
       CompanyName, 
        (SELECT AVG(Freight) 
        FROM Orders o 
        WHERE c.CustomerID = o.CustomerID) AS AvgFreight
FROM Customers c;

CustomerID	CompanyName	AvgFreight
ALFKI	Alfreds Futterkiste	37.6
ANATR	Ana Trujillo Emparedados y helados	24.4
ANTON	Antonio Moreno Taquería	38.4
...	...	...

子查詢的類型

非關聯子查詢

SELECT CustomerID
       ,CompanyName 
FROM Customers
WHERE CustomerID IN

         (SELECT CustomerID 
         FROM Orders 
         WHERE Freight > 800);

子查詢內「不」包含對外層查詢的參照
子查詢可獨立於外層查詢執行
常用於 WHERE 與 FROM

關聯子查詢

SELECT CustomerID, 
       CompanyName,

        (SELECT AVG(Freight) 
        FROM Orders o 
        WHERE c.CustomerID = o.CustomerID) AS AvgFreight

FROM Customers c;

子查詢內包含對外層查詢的參照
子查詢無法獨立於外層查詢執行
常用於 WHERE 與 SELECT

子查詢效能

關聯

子查詢會對外層查詢的每一列執行

非關聯

子查詢只執行一次，然後回傳結果給外層查詢

子查詢 vs. INNER JOIN

關聯子查詢

SELECT CustomerID, 
       CompanyName, 
        (SELECT AVG(Freight) 
        FROM Orders o 
        WHERE c.CustomerID = o.CustomerID) AS AvgFreight
FROM Customers c;

INNER JOIN

SELECT c.CustomerID,
       c.CompanyName, 
       AVG(o.Freight)
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID,
         c.CompanyName;

一起來練習吧！

改進 SQL Server 中的查詢效能