[解決済み] サブクエリを使用した派生テーブルとの内部結合

質問

環境 SQL 2008 R2

サブクエリを使用して派生テーブルを作成し、メインテーブルと結合しました。サブクエリは一度だけ実行されるのか、それとも結果セットの各行に対して実行されるのか知りたいのです。次のような例を考えてみましょう（参考までに架空のテーブル名）。

SELECT E.EID,DT.Salary FROM Employees E
INNER JOIN
(
    SELECT EID, (SR.Rate * AD.DaysAttended) Salary
    FROM SalaryRate SR
    INNER JOIN AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID

Inner Joinに使用されるサブクエリは一度だけ実行されるのですか、それとも複数回実行されるのですか？

上記のクエリをOUTER APPLYを使用して書き直すと、各行に対して確実にサブクエリが実行されることがわかります。下記を参照してください。

SELECT E.EID,DT.Salary FROM Employees E
OUTER APPLY
(
    SELECT (SR.Rate * AD.DaysAttended) Salary
    FROM SalaryRate SR
    INNER JOIN AttendanceDetails AD on AD.EID=SR.EID
    WHERE SR.EID=E.EID
) DT --Derived Table for outer apply

したがって、インナージョインがサブクエリを一度だけ実行するようにしたいだけです。

どのように解決するのですか？

まず注意すべきは、クエリが比較できないことです。 OUTER APPLY に置き換える必要があります。 CROSS APPLY または INNER JOIN と LEFT JOIN .

しかし、これらを比較できるようにすると、両方のクエリのクエリプランが同じであることがわかります。サンプルのDDLをモックアップしてみたところです。

CREATE TABLE #Employees (EID INT NOT NULL);
INSERT #Employees VALUES (0);
CREATE TABLE #SalaryRate (EID INT NOT NULL, Rate MONEY NOT NULL);
CREATE TABLE #AttendanceDetails (EID INT NOT NULL, DaysAttended INT NOT NULL);

以下を実行する。

SELECT E.EID,DT.Salary FROM #Employees E
OUTER APPLY
(
    SELECT (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
    WHERE SR.EID=E.EID
) DT; --Derived Table for outer apply

SELECT E.EID,DT.Salary FROM #Employees E
LEFT JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;

次のような計画を与える。

そしてINNER/CROSSに変更すること。

SELECT E.EID,DT.Salary FROM #Employees E
CROSS APPLY
(
    SELECT (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
    WHERE SR.EID=E.EID
) DT; --Derived Table for outer apply


SELECT E.EID,DT.Salary FROM #Employees E
INNER JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;

次のような計画を与える。

これらは、外側のテーブルにデータがなく、employeesに1行しかない場合のプランなので、あまり現実的ではありません。外部アプリケーションの場合、SQL Serverはemployeesに1行しかないと判断できるので、外部テーブルへのネストされたループジョイン（つまり行ごとのルックアップ）を行うだけでも有益です。employeesに1,000行を入れた後、LEFT JOIN/OUTER APPLYを使用すると、次のようなプランが得られます。

これは、SQL Serverが最適なプランとして、外側クエリを最初に実行し、結果をハッシュし、それからemployeesから検索することを決定したことを意味します。しかし、外側クエリからの述語はまだ使用できます。例えば、もし外側クエリが実行され、内部に保存されると、次のクエリは膨大なオーバーヘッドとなります。

SELECT E.EID,DT.Salary FROM #Employees E
LEFT JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID
WHERE E.EID = 1;

すべての従業員レートを検索し、結果を保存して、実際に1人の従業員を調べることに何の意味があるのでしょうか。実行計画を見てみると EID = 1 のテーブルスキャンに述語が渡される。 #AttendanceDetails :

ということで、以下の点についての回答です。

• 上記のクエリをOUTER APPLYを使用して書き直すと、各行に対して確実にサブクエリが実行されることがわかります。
• Inner Joinはサブクエリを一度だけ実行します。

以下の通りです。 . 使用方法 APPLY SQL Server は、可能であれば JOIN としてクエリを書き直そうとします。 OUTER APPLY は、クエリが各行に対して一度ずつ実行されることを保証するものではありません。同様に LEFT JOIN は、クエリーが一度だけ実行されることを保証するものではありません。

SQLは宣言型言語であり、どのように実行するかではなく、何を実行させたいかを指示します。

さらに、SQL Server はサブクエリを行列化せず、通常はメインクエリに定義が展開されます。

SELECT E.EID,DT.Salary FROM #Employees E
INNER JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;

実際に実行されるのは、もっとこうです。

SELECT  e.EID, sr.Rate * ad.DaysAttended AS Salary
FROM    #Employees e
        INNER JOIN #SalaryRate sr
            on e.EID = sr.EID
        INNER JOIN #AttendanceDetails ad
            ON ad.EID = sr.EID;