Ccmmutty logo
Commutty IT
・0 pv・10 min read

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

SQLPostgreSQLSQLServerOracle
https://cdn.magicode.io/media/notebox/blob_4zQOOoW

SQL ServerでのAPPLYの活用方法

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テーブル)

EmployeeIDFirstNameLastNameDepartmentIDHireDate
1JohnDoe12020-01-15
2JaneSmith12021-06-01
3MikeBrown22019-08-23
4LindaJohnson32020-11-11
5EmilyDavis42018-03-19
6MichaelTaylor22022-05-14
7SarahWilson32021-09-07
8DavidAnderson12020-12-25

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

SaleIDEmployeeIDSaleDateAmount
112023-01-101500.00
212023-03-152000.00
322023-02-051800.00
422023-06-182200.00
582023-05-201700.00
682023-07-222000.00
792023-07-221900.00
8102022-06-202200.00
9112021-05-192300.00
10122020-04-014000.00
11132019-03-023900.00
12142018-02-176000.00
13152018-01-189000.00
14162018-12-191000.00

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;
結果はこんな感じ
EmployeeIDFirstNameLastNameLatestSaleDateLatestAmountSaleRank
1JohnDoe2023-03-152000.001
2JaneSmith2023-06-182200.001
8DavidAnderson2023-07-222000.001
では結果が出たところで皆さん大好きな実行計画を見ていきましょう！
SSMSは珍しく実行計画がグラフィカルに出るので見やすいですね。
コストの下に秒数、その下に14中14(100%)見たいのがあると思います。
これがノード結果の行数だと思ってます。
INNER JOIN実行計画
上のルートを見るとソートなどがありウィンドウ関数のノードだとわかります。
追っていくと元表が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
;
結果はこんな感じで上記のINNER　JOINと同じ結果になりました。
EmployeeIDFirstNameLastNameLatestSaleDateLatestAmountSaleRank
1JohnDoe2023-03-152000.001
2JaneSmith2023-06-182200.001
8DavidAnderson2023-07-222000.001
では結果が出たところで実行計画を見比べてみましょう！
今度はルートが上下逆で表示されましたがノードから下がウィンドウ関数を行っているとわかります。
また一番根っこを見るとSales表からきているとわかりますね。
Cross Apply実行計画
ただこちらは一番根っこの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;
  • 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;

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);

Discussion

コメントにはログインが必要です。