【SQL】JOINで直積・順列・組み合わせを作る方法（CROSS JOIN・自己結合）

はじめに

この記事では、SQLのJOINを使って直積や順列、組み合わせを表現する方法を身近な例を使って紹介します。
SQLを使ってこのような操作をする機会は少ないかもしれませんが、統計的な計算を行う場面など、特定のケースでは頻出するため、考え方を理解しておくと応用の幅が広がります。

直積

サイズごとに値段が異なるTシャツを販売するとします。
Tシャツの値段、割引クーポンの種類は以下の通りです。

サイズ表

割引クーポン表

この2つのテーブルを使ってサイズごとにクーポンを適用した場合の金額を全パターン計算するにはどのようなSQL文が必要になるでしょうか。
このような2つの集合（今回の場合はサイズ表と割引クーポン表）から要素を1つずつ取り出し、全てのパターンのペアを作った時のペアの集合を「直積」と呼びます。今回のように直積を出したい場合にはCROSS JOINが適しています。

SELECT 
    サイズ表.サイズ, 
    割引クーポン表.種類 AS クーポンの種類, 
    (サイズ表.値段 - (サイズ表.値段 * 割引クーポン表.割引率)) AS 割引後価格
FROM サイズ表 
CROSS JOIN 割引クーポン表;

このようにCROSS JOINを使って2つのテーブルを結合することで、以下の通りサイズごとにクーポンを適用した場合の金額を全パターン計算することができます。

順列

次に、直積から自分自身とのペアを除外したい場合を考えます。
以下の配達センター表を元に、2拠点間の配送ルートを網羅した表を作成するとします。配送元カラムと配送先カラムの2カラムを用意して、拠点名を各カラムに入れるイメージです。ただし、「東京から東京」のような自分自身とのペアは除外します。

配達センター

SQLは以下のようになります。INNER JOINで同じテーブル同士を結合し、自分自身とのペアだけを除外するという書き方になります。

SELECT 
    配送元.名称 AS 配送元拠点, 
    配送先.名称 AS 配送先拠点
FROM 配達センター AS 配送元
INNER JOIN 配達センター AS 配送先
  ON 配送元.名称 <> 配送先.名称;

組み合わせ

先ほどの例では配達センターを「配送元」と「配送先」で区別したペアを作りましたが、要素の順番を区別せずに(A,B)のペアと(B,A)のペアを同じものとみなして重複分を削除した組み合わせを作るクエリを考えてみます。

組み合わせ1

以下のメンバー表があるとして、メンバーから2人組のペアを作る全てのパターンを一覧で出したい場合、どのようなクエリを作ることになるでしょうか。先ほどの例とは異なり、今回のケースでは(佐藤,山田)のペアと(山田,佐藤)のペアは同じものとみなすので、どちらか一方に統一させる必要があります。

メンバー表

このメンバー表からペアをつくる全てのパターンを作るクエリは以下の通りです。文字列に対して比較演算子を使うと辞書順で比較できることを利用して、自分自身とのペア（[佐藤,佐藤]）や順番を入れ替えただけの同じペア（[山田,佐藤]や[田中,佐藤]）を排除することができます。
出力を見ると、それぞれの名前は辞書順では佐藤<山田<田中という順番になっているようです。

SELECT 
    m1.名前 AS メンバー1,
    m2.名前 AS メンバー2
FROM メンバー表 m1
INNER JOIN メンバー表 m2 
   ON m1.名前 < m2.名前;

組み合わせ2

上の例では組み合わせ対象となるメンバーは「名前」という1つだけのキーをもっていましたが、キーの数が2つになる場合を考えてみます。

以下の座席一覧があるとして、コンサート会場の席を2席予約する全てのパターンを網羅した表を作りたい場合、どのようなクエリを実行する必要があるでしょうか。ただし、座席は「A列の1番」のように2つの成分で構成されているとします。以下の表では、「A列の1番」「A列の2番」「B列の1番」「B列の2番」と全部で4席存在することになります。

席

2席予約する全てのパターンを作るクエリは以下の通りです。キーの数が「列」と「番号」の2つになるため、条件が複雑になります。ONの1つ目の条件では、先ほどのメンバーの例と同様に「列」の大小関係で順序を固定しています。これにより同じ列同士のペアと、順番を入れ替えただけの同じペアを除外しています。ただし、列が同じでも番号が違う席は異なる席とみなせるため、ORを使って以下のように2つ目の条件を付け加えています。

SELECT 
    席1.列 AS 席1_列, 席1.番号 AS 席1_番号,
    席2.列 AS 席2_列, 席2.番号 AS 席2_番号
FROM 席 席1
JOIN 席 席2 
  ON (席1.列 < 席2.列)
  OR (席1.列 = 席2.列 AND 席1.番号 < 席2.番号);

以下の書き方は行値式と呼び、上記と同じ出力を得られます。

SELECT 
    席1.列 AS 席1_列, 席1.番号 AS 席1_番号,
    席2.列 AS 席2_列, 席2.番号 AS 席2_番号
FROM 席 席1
JOIN 席 席2 
  ON (席1.列, 席1.番号) < (席2.列, 席2.番号);

それでは、同じ座席表から3席を予約する全パターンを出力するにはどのようなクエリにする必要があるでしょうか。

SELECT 
    席1.列 AS 列1, 席1.番号 AS 番号1,
    席2.列 AS 列2, 席2.番号 AS 番号2,
    席3.列 AS 列3, 席3.番号 AS 番号3
FROM 席 AS 席1
JOIN 席 AS 席2 
  ON (席1.列 < 席2.列) 
     OR (席1.列 = 席2.列 AND 席1.番号 < 席2.番号)
JOIN 席 AS 席3 
  ON (席2.列 < 席3.列) 
     OR (席2.列 = 席3.列 AND 席2.番号 < 席3.番号);

このようにJOINを追加することで、以下の通り3席を予約する全パターンを得られます。

こちらも先ほど紹介した行値式を使うとより簡潔に実装することが可能です。

SELECT 
    席1.列 AS 列1, 席1.番号 AS 番号1,
    席2.列 AS 列2, 席2.番号 AS 番号2,
    席3.列 AS 列3, 席3.番号 AS 番号3
FROM 席 AS 席1
JOIN 席 AS 席2 
  ON (席1.列, 席1.番号) < (席2.列, 席2.番号)
JOIN 席 AS 席3 
  ON (席2.列, 席2.番号) < (席3.列, 席3.番号);

組み合わせ3

さらに問題を発展させて、以下のようにもし座席が1階席と2階席に分かれていたとして、2席選ぶにはどのような実装にする必要があるでしょうか。座席は列、番号に加えて「階」というキーをもつことになります。

通常の書き方では行数が増えて見通しが悪いクエリになってしまいますが、行値式で書くと以下のようにすっきりした形で実装できます。

SELECT 
    席1.階 AS 階1, 席1.列 AS 列1, 席1.番号 AS 番号1,
    席2.階 AS 階2, 席2.列 AS 列2, 席2.番号 AS 番号2
FROM 席 AS 席1
JOIN 席 AS 席2 
  ON (席1.階, 席1.列, 席1.番号) < (席2.階, 席2.列, 席2.番号);

最後に、3席分予約する全パターンを網羅するクエリは以下の通りです。

SELECT 
    席1.階 AS 階1, 席1.列 AS 列1, 席1.番号 AS 番号1,
    席2.階 AS 階2, 席2.列 AS 列2, 席2.番号 AS 番号2,
    席3.階 AS 階3, 席3.列 AS 列3, 席3.番号 AS 番号3
FROM 席 AS 席1
JOIN 席 AS 席2 
  ON (席1.階, 席1.列, 席1.番号) < (席2.階, 席2.列, 席2.番号)
JOIN 席 AS 席3 
  ON (席2.階, 席2.列, 席2.番号) < (席3.階, 席3.列, 席3.番号);

まとめ

この記事では、SQLのJOINを使った直積・順列・組み合わせの作り方と、行値式による条件式の簡潔な書き方を解説しました。
JOINはこのように単なるテーブル結合だけでなく、条件式を工夫することで順列や組み合わせも自然に表現できます。
この考え方を理解しておくことで、統計的な処理や網羅的な検証が必要な場面において、より応用の利くSQLを書くことができるようになるかと思います。