sqlでfrom句に複数テーブルを指定し、結合条件を記載しない場合の取得結果について

Question

sqlでfrom句に複数テーブルを記載し、テーブル同士の結合条件（onやwhereを用いる）を記載しない場合、取得できるレコードはどのようなものになるでしょうか？

SELECT
  * 
FROM
  Aテーブル
  , Bテーブル

※自分で調べた限りだと、「CROSS JOIN」もしくは「NATURAL JOIN」で結合した場合と同じ結果を返すと記載されているサイトがありました。

Answer 1

sqlでfrom句に複数テーブルを記載し、テーブル同士の結合条件（onやwhereを用いる）を記載しない場合、取得できるレコードはどのようなものになるでしょうか？

この質問の答えはすでにroot_jpさんやsk_3122さんの書かれているように、
AテーブルとBテーブルのすべての組み合わせのレコードが返されます。
レコード数はAテーブルのレコード数×Bテーブルのレコード数です。
呼び方は単純結合以外にもクロス結合とか直接結合と呼ばれるようです。

用途についてはOdacchiさんが「大量のテストデータ作成」と書かれていますが、
それ以外にもすべての組み合わせのパターンを取得したいときに使えるかもしれません。
例えばAテーブル(製品マスタ)、Bテーブル(色マスタ)とした場合に、
すべての製品と色の組み合わせが作り出せます。
（それが使えるかどうかはわかりませんが・・・）
他のサイトでは算数の九九表が作れるといった例もありました。

※自分で調べた限りだと、「CROSS JOIN」もしくは「NATURAL JOIN」で結合した場合と同じ結果を返すと記載されているサイトがありました。

CROSS JOINは訳すとクロス結合なので間違いないと思いますが、
NATURAL JOINは若干違います。
NATURAL JOINは自然結合と呼ばれます。以下のように規定されているようです。

自然結合は一般に、外部キー値から親キー値への照合に基づいた結合を指します。
ただし、SQL92 規格ではこの用語が少し違った意味で使用されています。
SQL92 規格では、同じ名前を持つ 2つの列が親キーと外部キーであるかのように照合されます。
つまり、そのデータベース設計が列名の一致は実際に親キーと外部キーをあらわすと取り決めている場合に、NATURAL JOIN 演算子を使用すべきだと、いう考え方です。

もう少しわかりやすく説明するとAテーブルとBテーブルとで同じ名前を持つ列で自動的に結合されます。

CROSS JOINもNATURAL JOINもテーブル同士の結合条件を記述しない点で共通していますが、
前者はすべての組み合わせを返すのに対し後者は内部で結合をしていますので異なります。
NATURAL JOINのSQL文付きでの説明は以下のサイトにあります。

CROSS JOIN / 自然結合 / 指定結合 / UNION JOIN

Comments

[wordconverter]

>nabe3様

ご回答ありがとうございます。

なるほど、本件のsqlは、｢CROSS JOIN｣で結合したものに該当し、｢NATURAL JOIN｣とは異なるということですね。下記のように解釈しました。

○CROSS JOIN
全ての組み合わせを取得するため、結合条件は指定しない

○NATURAL JOIN
結合キーを自動で識別し設定してくれるため、結合条件の指定の必要がない

詳しいご説明ありがとうございます。大変勉強になりました。
本件のsqlの使いどころ、｢CROSS JOIN｣と｢NATURAL JOIN｣の違いまで教えて頂いたため、ベストアンサーとさせていただきます。

Answer 2

単純結合といわれるものですね。

Aテーブル１行に対して、Bテーブルの全レコードが結合されてしまいます。
Aテーブルに２行あり、Bテーブルに３行ある場合

Aテーブルレコード１　　Bテーブルレコード１
Aテーブルレコード１　　Bテーブルレコード２
Aテーブルレコード１　　Bテーブルレコード３
Aテーブルレコード２　　Bテーブルレコード１
Aテーブルレコード２　　Bテーブルレコード２
Aテーブルレコード２　　Bテーブルレコード３

となり、計６行の結果となります。
とんでもないデータ数になってしまうので、単純結合を使う機会はありません。

Answer 3

どうなるかはほかの型がいっている通りです

使いどころは大量データ以外にも
UNPIVOTの代わりに横縦変換にも使えたりします

下のようにすると
KEY COL1 COL2 COL3
ーーーーーーーーーーーーーー
DATA AAA BBB CCC

のようなデータが
KEY POS VALUE
ーーーーーーーーーーーーーー
DATA 1 AAA
DATA 2 BBB
DATA 3 CCC

とすることができます

WITH SRC AS(
    SELECT 'DATA' KEY, 'AAA' COL1, 'BBB' COL2, 'CCC' COL3
)
SELECT
      SRC.KEY
    , MAP.POS
    , CASE MAP.POS
        WHEN 1 THEN SRC.COL1
        WHEN 2 THEN SRC.COL2
        WHEN 3 THEN SRC.COL3
      END VALUE
FROM SRC
   , (
    SELECT 1 POS
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
) MAP

どうなっているかというと
結合によって下のような票ができるので
POSが指す位置のデータを取っています

SRC X MAP
KEY COL1 COL2 COL3 POS
ーーーーーーーーーーーーーー
DATA AAA BBB CCC 1
DATA AAA BBB CCC 2
DATA AAA BBB CCC 3

Comments

[wordconverter]

kutsulog様

ご回答ありがとうございます。

なるほど、これはトリッキなー使い方ですね。横縦変換したい対象のテーブルの列数分だけ、一時テーブルにインデックス行を持てば、両者の掛け算で目的の行が抽出可能となるわけですね。

数学の行列みたいですね。

Answer 4

詳細は、先に投稿されてる方がおっしゃる通りです。

用途としては、（意味のないレコードでも構わないという前提で）大量にテスト用のレコードが欲しいときなどにたまに使います。

具体的には、このSELECT分をINSERT文の副問い合わせにすることで、簡単に大量のレコードが作成できます。

Comments

[wordconverter]

Odacchi様

ご回答ありがとうございます。

テスト用に大量データを作成する際に手軽に使えそうですね。使う場面がイメージしやすかったです。ご説明ありがとうございました

Answer 5

「Aテーブルの行数 × Bテーブルの行数」分だけ、レコードが返ります。

【Aテーブル】
   valA
  ------
   A-1
   A-2
   A-3

【Bテーブル】
   valB
  ------
   B-11
   B-22

SELECT * FROM Aテーブル, Bテーブル

   valA | valB
  ------+------
   A-1 | B-11
   A-2 | B-11
   A-3 | B-11
   A-1 | B-22
   A-2 | B-22
   A-3 | B-22

すべての組み合わせが返る、と言えば良いのか･･･