OUTER APPLY で SELECT 項目で取得したい内容を JOIN の ON にも使用する

最近ようやく OUTER APPLY の使い方がわかってきたマンなので書いてみます。

以下、記事内では OUTER APPLY を使用していますが、今回のように確実に APPLY 先のレコードが取得できることが分かっている場合は CROSS APPLY を使用したほうが高パフォーマンスになります。

通常、SELECT 句で CASE 式などを使った複雑な値を取得する場合、その値を JOIN の ON 句で使うことはできません。

SELECT CASE M.Code
           WHEN 0 THEN 'A'
           WHEN 1 THEN 'B'
           ELSE        'C'
       END AS XCode
      ,S.Name AS XName

FROM Main AS M

INNER JOIN Sub AS S
-- XCode をここで使うことは出来ないので同じ CASE 式を書かないといけない
        ON S.Code = CASE M.Code
                        WHEN 0 THEN 'A'
                        WHEN 1 THEN 'B'
                        ELSE        'C'
                    END

※ そもそもそのデータ設計どうなんだというツッコミは置いておいてください。

ここで、OUTER APPLY を使うとスマートに解決することができます。

SELECT X.XCode AS XCode
      ,S.Name AS XName

FROM Main AS M

OUTER APPLY (
    SELECT CASE M.Code
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        ELSE        'C'
    END AS XCode
) AS X
INNER JOIN Sub
        ON X.XCode = Sub.Code

私はわからないことがあるとき LINQ で喩えて考えるというライフハックを持っているので、それで理解してみました。
LINQ でいうところの let 句に近いと思います。

var q = from m in Main
        let x = new
                {
                    XCode = m.Code == 0 ? "A" :
                            m.Code == 1 ? "B" :
                                          "C"
                }
        join s in Sub
          on x.XCode equals s.Code
        select new
        {
            XCode = x.XCode,
            XName = s.Name
        };

「各レコードに対して "そのレコードについて処理されるコンテキスト" で使う値を選択する」と言えばいいでしょうか。

ちなみに、他の方法として CTE (Common Table Expression) を使う方法やインラインテーブル値関数を定義する方法もあると思うのでお好みで。

今回は名前を付けられるような意味のある CASE 文ではなく、その箇所に特化した特殊な CASE 文だったので無名で処理したくて OUTER APPLY を使う方法をとりました。闇。

えすくーえるは苦手なので、もっといいやり方があれば教えてください。