CROSS JOIN （クロス結合）を使ってデータを取得する
CROSS JOIN （クロス結合）を使ってデータを取得する
前回は 「 FULL (OUTER) JOIN （完全外部結合）を使ってデータを取得する 」 で、FULL JOIN を使って、二つのテーブルを結合してデータを取得してみました。
今回は、今までの JOIN とはちょっと違った CROSS JOIN （クロス結合） を使って、二つのテーブルを結合して値を取得してみましょう。
こちら のスクリプトを実行して、Student・Test・TestResult テーブルを生成しておいてください。
CROSS JOIN （クロス結合）
CROSS JOIN は、INNER JOIN や LEFT JOIN と違って、ON でマッチの条件を指定しません。
[左テーブル] CROSS JOIN [右テーブル] のように結合すると、[左テーブル] と [右テーブル]の両方のテーブルの、全てのコンビネーションの行の結果セットを取得することができます。
ですので、結果で得られるレコード数は [ 左のテーブルのレコード数 ] x [ 右のテーブルのレコード数 ] になります。
次のような Test テーブルと Student テーブルがあります。
[ Test テーブル ]
[ Student テーブル ]
CROSS JOIN を使って、Test テーブルと Student テーブルを結合した結果は以下の通りです。
SELECT * FROM Test AS T CROSS JOIN Student AS S;
[ 実行結果 ]
Test テーブルの行数 2 * Student テーブルの行数 6 = 12 レコードで、全てのコンビネーションの行の結果セットが取得できました。
このように各テスト、全生徒の一覧表を作りたいような時に便利です。
CROSS JOIN の使用例を見てみましょう。 Test テーブルに 「 History 1 」 を追加します。 TestID = 3 で挿入されました。
INSERT INTO Test ( TestNameEn, TestNameJp ) VALUES ( 'History 1', N'歴史１');
[ 実行結果 ]
この時点で、TestResult テーブルは以下の通りで、TestResult 入っていない、Test テーブルと Student テーブルのコンビネーションを探してみましょう。
[ TestResult テーブル ]
次のクエリーでは、CROSS JOIN を使って全パターンを取得し、TestResult テーブルに存在しないレコードを選択しています。
SELECT * FROM Test AS T CROSS JOIN Student AS S WHERE NOT EXISTS (SELECT * FROM TestResult AS TR WHERE TR.TestID = T.TestID AND TR.StudentID = S.StudentID);
[ 実行結果 ]
TestResult テーブルに存在していなかった、コンビネーションを取得することができましたね。
おまけですが、この結果を TestResult テーブル に挿入して、TestResult テーブルが、Test テーブルと Student テーブルの全てのコンビネーションのレコードを生成するには次のように Insert できます。
INSERT INTO TestResult ( TestID, StudentID ) SELECT T.TestID, S.StudentID FROM Test AS T CROSS JOIN Student AS S WHERE NOT EXISTS (SELECT * FROM TestResult AS TR WHERE TR.TestID = T.TestID AND TR.StudentID = S.StudentID);
[ 実行結果 ]
