【SQLServer】APPLYという結合を初めて見たので概要メモ

SQL Serverでの`APPLY`の活用方法

APPLYには2種類ありOUTER APPLYとCROSS APPLYがある。それぞれ
OUTER APPLYはLEFT JOINのように動作し
OUTER APPLYはINNER JOINのように動作する。
但し、結合時に左辺と右辺の表を結合するときに結合される右辺の表を結合前に左辺の内容で絞ってくれるらしい。

1. `OUTER APPLY`とは？

OUTER APPLYは、LEFT JOINのように動作し、外部クエリの各行に対してサブクエリやテーブル値関数の結果を結び付けます。
OUTER APPLYは、外部クエリの行がサブクエリと結びつかない場合でも、その行を結果セットに含めます。

2. `CROSS APPLY`とは？

CROSS APPLYは、INNER JOINのように動作し、外部クエリの各行に対してサブクエリやテーブル値関数の結果を結び付けます。
ただし、CROSS APPLYは、外部クエリの行がサブクエリと結びつかない場合、その行は結果セットに含まれません。
言い換えれば、CROSS APPLYは、サブクエリが結果を返す場合にのみ行を結びつけ、結果セットに追加します。

3. サンプルデータ

以下のような社員情報を持つEmployeesテーブルと、売上情報を持つSalesテーブルを用意します。

このDBの問題点は従業表に存在しないEmployeesIDが売上表から削除されていないことです。
辞職した方のIDが売上表に残ってるみたいなイメージです。
どんなデータ構造やねん。とはなりますが、あくまでサンプルなのでそこは何となく察してください。

お試しの方はSQLを記事下部に貼るのでそのままSQLを流せば大丈夫です。

従業員テーブル(Employeesテーブル)

売上テーブル(Salesテーブル)

3. クエリで動きを確認

INNER JOIN実行結果

抽出するデータは売上表(Sales )に売上データが存在する従業員を取得する。同時に従業員の最新の売上を抽出します。

まずはINNER JOINを使って従業員表(Employees )と売上表(Sales )を結合します。

SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    s.LatestSaleDate, 
    s.LatestAmount,
    s.SaleRank
FROM 
    Employees e
INNER JOIN (
    SELECT 
        * 
        FROM(
            SELECT 
                EmployeeID, 
                SaleDate AS LatestSaleDate, 
                Amount AS LatestAmount,
                RANK() OVER (PARTITION BY EmployeeID ORDER BY SaleDate DESC) AS SaleRank
            FROM 
                Sales 
            ) s
        WHERE SaleRank = 1 
) s ON e.EmployeeID = s.EmployeeID;SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    s.LatestSaleDate, 
    s.LatestAmount,
    s.SaleRank
FROM 
    Employees e
INNER JOIN (
    SELECT 
        * 
        FROM(
            SELECT 
                EmployeeID, 
                SaleDate AS LatestSaleDate, 
                Amount AS LatestAmount,
                RANK() OVER (PARTITION BY EmployeeID ORDER BY SaleDate DESC) AS SaleRank
            FROM 
                Sales 
            ) s
        WHERE SaleRank = 1 
) s ON e.EmployeeID = s.EmployeeID;

結果はこんな感じ

では結果が出たところで皆さん大好きな実行計画を見ていきましょう！
SSMSは珍しく実行計画がグラフィカルに出るので見やすいですね。
コストの下に秒数、その下に14中14(100%)見たいのがあると思います。
これがノード結果の行数だと思ってます。

上のルートを見るとソートなどがありウィンドウ関数のノードだとわかります。
追っていくと元表がSales 表からきていることがわかります。
Sales 表から14レコード(全行)をウィンドウ関数の集計にかけたりフィルターをかけたりと
11行まで絞り最後にINNERJOINで3行になっているように見えますね。

続いてCROSS APPLYを用いて実行します

CROSS APPLY実行結果

SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    --d.DepartmentName,
    samary.LatestSaleDate, 
    samary.LatestAmount,
    samary.SaleRank
FROM 
    Employees e
CROSS APPLY (
    SELECT * FROM (
        SELECT 
            SaleDate AS LatestSaleDate, 
            Amount AS LatestAmount,
            RANK() OVER (PARTITION BY s.EmployeeID ORDER BY s.SaleDate DESC) AS SaleRank
        FROM 
            Sales s
        WHERE 
            s.EmployeeID = e.EmployeeID
        ) s
    WHERE s.SaleRank = 1

) samary
;SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    --d.DepartmentName,
    samary.LatestSaleDate, 
    samary.LatestAmount,
    samary.SaleRank
FROM 
    Employees e
CROSS APPLY (
    SELECT * FROM (
        SELECT 
            SaleDate AS LatestSaleDate, 
            Amount AS LatestAmount,
            RANK() OVER (PARTITION BY s.EmployeeID ORDER BY s.SaleDate DESC) AS SaleRank
        FROM 
            Sales s
        WHERE 
            s.EmployeeID = e.EmployeeID
        ) s
    WHERE s.SaleRank = 1

) samary
;

結果はこんな感じで上記のINNER　JOINと同じ結果になりました。

では結果が出たところで実行計画を見比べてみましょう！
今度はルートが上下逆で表示されましたがノードから下がウィンドウ関数を行っているとわかります。
また一番根っこを見るとSales表からきているとわかりますね。

ただこちらは一番根っこのSales表の時点で16中6レコードとすでに絞られています。こうすることでウィンドウ関数による集約やソートが実行されるレコード数を減らすことができます。
要はパフォーマンスが向上しているということなのか。。。
実際、今回はINDEX等を貼っていませんが高速化が厄介なウィンドウ関数や集約関数では結合時にAPPLYを使うことで
INDEXの効果を受けることができそうですね。
これが外部クエリの各行に対してサブクエリの結果を結び付けるということなのですね。

4. PostgresやORACLEで同機能を使う

APPLYはSQLServerの機能となるので他のDBでも調べてみました。

Postgresの場合 LATERALを使用する。若干クエリ内容は違いますが下記に手を加えていけば同じ結果が得られます。

SELECT e.EmployeeID, e.FirstName, e.LastName, s.LatestSaleDate, s.LatestAmount
FROM Employees e
LEFT JOIN LATERAL (
    SELECT SaleDate AS LatestSaleDate, Amount AS LatestAmount
    FROM Sales s
    WHERE s.EmployeeID = e.EmployeeID
    ORDER BY SaleDate DESC
    LIMIT 1
) s ON true;SELECT e.EmployeeID, e.FirstName, e.LastName, s.LatestSaleDate, s.LatestAmount
FROM Employees e
LEFT JOIN LATERAL (
    SELECT SaleDate AS LatestSaleDate, Amount AS LatestAmount
    FROM Sales s
    WHERE s.EmployeeID = e.EmployeeID
    ORDER BY SaleDate DESC
    LIMIT 1
) s ON true;

ORACLEの場合
同様の機能は見つけられませんでした。
Oracleでは、OUTER APPLYに相当する機能をOUTER JOINとサブクエリを組み合わせて実現する形になりますね

SELECT e.EmployeeID, e.FirstName, e.LastName, s.LatestSaleDate, s.LatestAmount
FROM Employees e
LEFT JOIN (
    SELECT EmployeeID, SaleDate AS LatestSaleDate, Amount AS LatestAmount
    FROM (
        SELECT s.EmployeeID, s.SaleDate, s.Amount,
               ROW_NUMBER() OVER (PARTITION BY s.EmployeeID ORDER BY s.SaleDate DESC) as rn
        FROM Sales s
    )
    WHERE rn = 1
) s ON e.EmployeeID = s.EmployeeID;SELECT e.EmployeeID, e.FirstName, e.LastName, s.LatestSaleDate, s.LatestAmount
FROM Employees e
LEFT JOIN (
    SELECT EmployeeID, SaleDate AS LatestSaleDate, Amount AS LatestAmount
    FROM (
        SELECT s.EmployeeID, s.SaleDate, s.Amount,
               ROW_NUMBER() OVER (PARTITION BY s.EmployeeID ORDER BY s.SaleDate DESC) as rn
        FROM Sales s
    )
    WHERE rn = 1
) s ON e.EmployeeID = s.EmployeeID;

5. まとめ

APPLYは結合時に右表を左表の内容で絞ることができるというイメージ
PostgresはLATERAL
Oracleはサブクエリとかつかって上手いことやる

初学者の為、ちょこちょこミスとかがあるかもしれませんが
ご容赦ください。そこらへんは後々修正していきます。
こんな感じで知ったことを記事でアウトプットするのはよいですね。
今後も初見のことや学んでいることを記事(メモ)してこうと思います。

サンプルデータSQL

SSMSでそのまま流せば大丈夫ですがSQLcmdなど使う場合はそれ用に加工して下さい ORACLEやPostgresに入れる場合にはデータ型や文法を適宜変えればいけます。

-- 社員テーブルの作成
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    HireDate DATE
);

-- 売上テーブルの作成
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    EmployeeID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

-- サンプルデータの投入
-- 社員データの挿入
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, HireDate) VALUES
(1, 'John', 'Doe', 1, '2020-01-15'),
(2, 'Jane', 'Smith', 1, '2021-06-01'),
(3, 'Mike', 'Brown', 2, '2019-08-23'),
(4, 'Linda', 'Johnson', 3, '2020-11-11'),
(5, 'Emily', 'Davis', 4, '2018-03-19'),
(6, 'Michael', 'Taylor', 2, '2022-05-14'),
(7, 'Sarah', 'Wilson', 3, '2021-09-07'),
(8, 'David', 'Anderson', 1, '2020-12-25');

-- 売上データの挿入
INSERT INTO Sales (SaleID, EmployeeID, SaleDate, Amount) VALUES
(1, 1, '2023-01-10', 1500.00),
(2, 1, '2023-03-15', 2000.00),
(3, 2, '2023-02-05', 1800.00),
(4, 2, '2023-06-18', 2200.00),
(5, 8, '2023-05-20', 1700.00),
(6, 8, '2023-07-22', 2000.00),
(7, 9, '2023-07-22', 1900.00),
(8, 10, '2022-06-20', 2200.00),
(9, 11, '2021-05-19', 2300.00),
(10,12, '2020-04-01', 4000.00),
(11,13, '2019-03-02', 3900.00),
(12,14, '2018-02-17', 6000.00),
(13,15, '2018-01-18', 9000.00),
(14,16, '2018-12-19', 1000.00);-- 社員テーブルの作成
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    HireDate DATE
);

-- 売上テーブルの作成
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    EmployeeID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

-- サンプルデータの投入
-- 社員データの挿入
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, HireDate) VALUES
(1, 'John', 'Doe', 1, '2020-01-15'),
(2, 'Jane', 'Smith', 1, '2021-06-01'),
(3, 'Mike', 'Brown', 2, '2019-08-23'),
(4, 'Linda', 'Johnson', 3, '2020-11-11'),
(5, 'Emily', 'Davis', 4, '2018-03-19'),
(6, 'Michael', 'Taylor', 2, '2022-05-14'),
(7, 'Sarah', 'Wilson', 3, '2021-09-07'),
(8, 'David', 'Anderson', 1, '2020-12-25');

-- 売上データの挿入
INSERT INTO Sales (SaleID, EmployeeID, SaleDate, Amount) VALUES
(1, 1, '2023-01-10', 1500.00),
(2, 1, '2023-03-15', 2000.00),
(3, 2, '2023-02-05', 1800.00),
(4, 2, '2023-06-18', 2200.00),
(5, 8, '2023-05-20', 1700.00),
(6, 8, '2023-07-22', 2000.00),
(7, 9, '2023-07-22', 1900.00),
(8, 10, '2022-06-20', 2200.00),
(9, 11, '2021-05-19', 2300.00),
(10,12, '2020-04-01', 4000.00),
(11,13, '2019-03-02', 3900.00),
(12,14, '2018-02-17', 6000.00),
(13,15, '2018-01-18', 9000.00),
(14,16, '2018-12-19', 1000.00);

If this story triggered your interest, why don't you come and visit us?

Go/Pythonへ挑戦！モダン技術でキャリアの第二ステージを共に！

株式会社ジェネスティコンサルティング's job postings

【SQLServer】APPLYという結合を初めて見たので概要メモ

淵之上玲至

株式会社ジェネスティコンサルティング / エンジニア

技術技術ブログ SQL Server

株式会社ジェネスティコンサルティング

【SQLServer】APPLYという結合を初めて見たので概要メモ

SQL Serverでの`APPLY`の活用方法

目次

1. `OUTER APPLY`とは？

2. `CROSS APPLY`とは？

3. サンプルデータ

3. クエリで動きを確認

4. PostgresやORACLEで同機能を使う

5. まとめ

サンプルデータSQL

ジェネスティ技術ブログ

株式会社ジェネスティコンサルティング

【SQLServer】APPLYという結合を初めて見たので概要メモ

SQL ServerでのAPPLYの活用方法

目次

1. OUTER APPLYとは？

2. CROSS APPLYとは？

3. サンプルデータ

3. クエリで動きを確認

4. PostgresやORACLEで同機能を使う

5. まとめ

サンプルデータSQL

ジェネスティ技術ブログ

技術

Weekly ranking

SQL Serverでの`APPLY`の活用方法

1. `OUTER APPLY`とは？

2. `CROSS APPLY`とは？