サブクエリ

SQL Serverでクエリ性能を改善する

Dean Smith

Founder, Atamai Analytics

サブクエリの見た目は？

サブクエリ

サブクエリの見た目は？

外側クエリとのサブクエリ

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 でのサブクエリ

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 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でクエリ性能を改善する