【SQL】CROSS JOIN(クロス結合)の使い方
クロス結合ってどんな結合?

通常の「ON」での結合条件を使用しない結合。

結合する両テーブル、すべての組み合わせパターンのレコードを取得します。
なので、結果で得られるレコード数は [ 左のテーブルのレコード数 ] x [ 右のテーブルのレコード数 ] になります。


例えば、以下のようなテーブルA、Bがあった場合

テーブルA:

名前
山田さん
鈴木さん
田中さん

テーブルB:

クラス
りんご
バナナ
みかん
// AとBをクロス結合する
SELECT *
  FROM Aテーブル
 CROSS JOIN Bテーブル

結合結果:3レコード(Aテーブル) × 3レコード(Bテーブル)

名前クラス
山田さんりんご
山田さんバナナ
山田さんみかん
鈴木さんりんご
鈴木さんバナナ
鈴木さんみかん
田中さんりんご
田中さんバナナ
田中さんみかん

のようなテーブルができます。
この例では、あまり実用性が感じられませんので、 実例を用いて説明してみます。

以下のようなテーブルがあったとします。

工事テーブル:  

No工事名金額年度
1工事A100円2018
2工事B200円2018
3工事C300円2018

支払テーブル:

工事名金額年度
工事A100円2018
工事A500円2017
工事B200円2018
工事B500円2017
工事C300円2018
工事C100円2017

工事テーブルに存在する各工事の支払金額を、支払テーブルに存在する全年度分集計したいとき 工事テーブルを軸に支払テーブルを結合し、各工事毎に集計します。

テーブル結合条件を「工事名」にleftjoinした場合、以下のようになり左のレコードが重複してしまいます。

No工事名金額年度工事名金額年度
1工事A100円2018工事A100円2018
1工事A100円2018工事A500円2017

工事テーブルA.金額を集計すると、重複して計上されてしまいます。
(実際はテーブルB.金額を合計すれば良いですが、ここではあえてクロス結合で)

そこで、2つのテーブルをクロス結合すると以下のような表になります。

No工事名金額年度工事名金額年度
1工事A100円2018工事A100円2018
1工事A100円2018工事A500円2017
1工事B100円2018工事A200円2018
1工事B100円2018工事A500円2017
1工事C100円2018工事A300円2018
1工事C100円2018工事A100円2017
2工事A100円2018工事A100円2018
2工事A100円2018工事A500円2017
2工事B100円2018工事A200円2018
2工事B100円2018工事A500円2017
2工事C100円2018工事A300円2018
2工事C100円2018工事A100円2017
3工事A100円2018工事A100円2018
3工事A100円2018工事A500円2017
3工事B100円2018工事A200円2018
3工事B100円2018工事A500円2017
3工事C100円2018工事A300円2018
3工事C100円2018工事A100円2017

このままじゃ、単に結合しただけなので、以下のように集計します。

①工事テーブルのNoを、支払テーブルとクロス結合して支払テーブルにNoを付与する。
②支払テーブルから工事名ごとに抽出し、Noを結合条件に工事テーブルと結合する

select sum(金額) as 支払合計
  from (select No
          from 工事テーブル
      CROSS JOIN (select *
              from 支払テーブル
            )工事      
               left join (select 1 as No
                      ,SA.*
                   from 支払テーブル SA
                  where 工事名 = "工事A"
                 )支払A
             on 工事.No = 支払A.No
           left join (select 2 as No
                       ,SB.*
                    from 支払テーブル SB
                   where 工事名 = "工事B"
                 )支払B
            on 工事.No = 支払B.No
           left join (select 3 as No
                     ,SC.*
                   from 支払テーブル SC
                  where 工事名 = "工事C"
                  )支払C
            on 工事.No = 支払C.No
     )合計

工事テーブルに存在する全工事の、支払レコードが重複なしにできます。  

No工事名金額年度
1工事A100円2018
1工事A500円2017
2工事B200円2018
2工事B500円2017
3工事C300円2018
3工事C100円2017

支払合計 1700円になり、重複なしの金額が集計できます。
まどろっこしい書き方をしましたが、レコードをSEQ毎に複製したいときなど、便利です。


今日はここまで。