明けましておめでとうございます。
滅多に更新しないブログですが、本年もよろしくお願いいたします。

今回は直積シリーズの第二弾。直積をイメージする助けになりそうな、とある使い方を紹介します。

まずは以下のテーブルを用意します。
超簡単なテーブルです。

DROP TABLE crossjoin_test;

CREATE TABLE crossjoin_test (
id number(1)
);

中に入れるのは以下のデータです。

TRUNCATE TABLE crossjoin_test;

INSERT INTO crossjoin_test VALUES( 1 );
INSERT INTO crossjoin_test VALUES( 2 );
INSERT INTO crossjoin_test VALUES( 3 );
INSERT INTO crossjoin_test VALUES( 4 );
INSERT INTO crossjoin_test VALUES( 5 );
INSERT INTO crossjoin_test VALUES( 6 );
INSERT INTO crossjoin_test VALUES( 7 );
INSERT INTO crossjoin_test VALUES( 8 );
INSERT INTO crossjoin_test VALUES( 9 );

COMMIT;

超簡単なテーブルが用意出来ました。
次に、以下のSQLを実行してみます。

SELECT * FROM crossjoin_test t1
INNER JOIN crossjoin_test t2 ON t1.id=t2.id
ORDER BY 1
;

/* Oracle的に書くと */
SELECT * FROM crossjoin_test t1,crossjoin_test t2
WHERE t1.id = t2.id
ORDER BY 1
;

こちらが実行結果です。

ID ID
-- --
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

どうという事はない結果です。

ちなみに、一つのテーブルをこのように2回登場させることができます。

では次に直積を試してみます。

SELECT * FROM crossjoin_test t1
CROSS JOIN crossjoin_test t2
ORDER BY 1,2
;

/* Oracle的に書くと */
SELECT * FROM crossjoin_test t1,crossjoin_test t2
ORDER BY 1,2
;

結果は以下の様になります。

ID ID
-- --
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
:
( 省略 )
:
8 6
8 7
8 8
8 9
9 1
9 2
9 3
9 4
9 5
9 6
9 7
9 8
9 9

81行が選択されました。

当たり前の結果です。

次は少し遊んでみましょう。

SELECT concat(t1.id , ' × ' , t2.id , ' = ' , t1.id * t2.id )
FROM crossjoin_test t1
CROSS JOIN crossjoin_test t2
ORDER BY 1
;

/* Oracle的に書くと */
SELECT t1.id||' × '||t2.id||' = '||t1.id*t2.id
FROM crossjoin_test t1,crossjoin_test t2
ORDER BY 1
;

実行結果が以下です。

T1.ID||'×'||T2.ID||'='||T1.ID*T2.ID
----------------------------------------
1 × 1 = 1
1 × 2 = 2
1 × 3 = 3
1 × 4 = 4
1 × 5 = 5
1 × 6 = 6
1 × 7 = 7
1 × 8 = 8
1 × 9 = 9
2 × 1 = 2
2 × 2 = 4
2 × 3 = 6
:
( 省略 )
:
8 × 6 = 48
8 × 7 = 56
8 × 8 = 64
8 × 9 = 72
9 × 1 = 9
9 × 2 = 18
9 × 3 = 27
9 × 4 = 36
9 × 5 = 45
9 × 6 = 54
9 × 7 = 63
9 × 8 = 72
9 × 9 = 81


81行が選択されました。

九九の表が出来ました。

直積でコードを生成する

上記の九九の様な使い方、あまりしないと思いますが、便利なときもあります。

上記の例は、9行のテーブルを2回使いましたが、これに0の行を追加します。

INSERT INTO crossjoin_test VALUES( 0 );
COMMIT;

これで、0～9の10行のデータが入りました。

そして、一つのテーブルを3回使用する、以下のようなSQLを書いて実行します。

SELECT concat(t1.id , t2.id , t3.id )
FROM crossjoin_test t1
CROSS JOIN crossjoin_test t2
CROSS JOIN crossjoin_test t3
ORDER BY 1
;

/* Oracle的に書くと */
SELECT t1.id||t2.id||t3.id
FROM crossjoin_test t1,crossjoin_test t2,crossjoin_test t3
ORDER BY 1
;

実行結果が以下となります。

T1.ID||T2.ID||T3.ID
----------------------
000
001
002
003
004
005
006
:
(省略)
:
997
998
999

1000行が選択されました。

例えば、トランザクションを全てUPDATE文で処理したいような場合、テーブルの中にあらかじめすべてのデータを入れておくという事をすることがあります。

そのような場合に、この方法で簡単にコード値を作成して、これを基にINSERTしておくことができます。

あらかじめテーブルにデータを入れておくというのは、SQL文がシンプルになるという事もありますし、ストレージ内でデータのフラグメンテーションが生じるのを抑制したい場合などが考えられます。

最後は少し話がそれましたが、直積をきちんと理解すると、RDBの理解につながると思います。

( 2021.01.20 )

select文を投げたら帰ってこない。
insertのバッチ処理が容量オーバーになった。

誰しもそういう経験があると思います。

Joinのキーが不足していたために、直積で膨大な件数が発生するという原因です。

直積とは

以下のケースで考えてみます。

社員テーブル(syain_tb)と銀行テーブル(bank_tb)から、社員ごとの給与振込口座一覧表を作成するというものです。

まずは準備のスクリプトです。

CREATE TABLE syain_tb (
syain_id CHAR(2),
syain_name VARCHAR(10),
bank_cd CHAR(4),
branch_cd CHAR(3)　 );

CREATE TABLE bank_tb (
bank_cd CHAR(4),
branch_cd CHAR(3),
branch_name VARCHAR(30)　 );

INSERT INTO syain_tb VALUES( '01','山田','0001','002');
INSERT INTO syain_tb VALUES( '02','鈴木','0001','003');
INSERT INTO syain_tb VALUES( '03','田中','0002','001');

INSERT INTO bank_tb VALUES( '0001','001','りんご銀行本店');
INSERT INTO bank_tb VALUES( '0001','002','りんご銀行青森支店');
INSERT INTO bank_tb VALUES( '0001','003','りんご銀行長野支店');
INSERT INTO bank_tb VALUES( '0002','001','みかん銀行本店');
INSERT INTO bank_tb VALUES( '0003','001','いちご銀行本店');

COMMIT;

社員ごとの給与振込口座一覧を出力するには、
syain_tbの bank_cd と、bank_tbの bank_cd
syain_tbの branch_cd と、bank_tbの branch_cd
をJOINします。

SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s
INNER JOIN bank_tb b ON s.bank_cd = b.bank_cd
　　　　　 AND s.branch_cd = b.branch_cd
ORDER BY 1 ;

/* Oracle的に書くと */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s,bank_tb b
WHERE s.bank_cd = b.bank_cd
AND s.branch_cd = b.branch_cd
ORDER BY 1 ;

いずれも結果は以下となります。

01 山田 0001 002 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行長野支店
03 田中 0002 001 みかん銀行本店

ここで、JOINのキーが足りないとどうなるでしょうか?
branch_cd をJOINのキーから外してみます。

/* branch_cdのJOINをコメントアウトしました */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s,bank_tb b
WHERE s.bank_cd = b.bank_cd
--AND s.branch_cd = b.branch_cd
ORDER BY 1
;

/* Oracleの書き方 */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s
INNER JOIN bank_tb b ON s.bank_cd = b.bank_cd
-- AND s.branch_cd = b.branch_cd
ORDER BY 1
;

以下が検索結果です。

01 山田 0001 002 りんご銀行長野支店
01 山田 0001 002 りんご銀行本店
01 山田 0001 002 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行本店
02 鈴木 0001 003 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行長野支店
03 田中 0002 001 みかん銀行本店

bank_cdしかJOINのキーに入っていないので、同一銀行で複数の支店のあるりんご銀行に口座をもつ山田さんと鈴木さんが3行ずつ出ています。

これが直積です。

上の例では、3行出力すべきところが、7行になっているだけですが、JOINしているテーブルによっては、本来なら100件の筈が10億件になって、バッチ処理が止まるという事になります。

くれぐれも、お気を付けください。

SQLが間違っていても、データによっては発生しない

そうなのです。
たとえ、SQL文に問題があったとしても、データによっては、出力結果の増殖は発生しません。

上記の出力結果で、田中さんについては正しい結果が表示されています。

たとえば、リリースしたばかりで、テーブルにデータが1日分しかないとか、そういう場合は日付の指定が漏れていると、リリース日の夜間ではなくて、翌日の夜間処理で悲惨な結果が発生します。
あるいは、期の指定がないと、リリースして半年たって、期が変わって世代データが発生した時に夜間処理が終わらない事になります。

くれぐれもお気を付けください。

直積こそ、リレーショナルDBを理解する肝

さて、これはぜひ言いたい。この記事で一番言いたいのはこれです。

そもそも、JOINの指定が何もなくて、完全に直積になっている状態。
その状態に対して条件を指定していく。

そう考えると、SQLはイメージしやすくなります。

フルに直積された状態から、
syain_tbのbank_cd列とbank_tb のbank_cd列の等しいものを抽出する。
さらに、
syain_tbのbranch_cd列とbank_tb のbranch_cd列の等しいものを抽出する。
というイメージです。

これは断然Oracleの書き方の方が分かりやすいです。

/* WHERE句なし */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s,bank_tb b
ORDER BY 1
;

01 山田 0001 002 りんご銀行青森支店
01 山田 0001 002 りんご銀行本店
01 山田 0001 002 りんご銀行長野支店
01 山田 0001 002 みかん銀行本店
01 山田 0001 002 いちご銀行本店
02 鈴木 0001 003 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行本店
02 鈴木 0001 003 りんご銀行長野支店
02 鈴木 0001 003 いちご銀行本店
02 鈴木 0001 003 みかん銀行本店
03 田中 0002 001 りんご銀行本店
03 田中 0002 001 りんご銀行青森支店
03 田中 0002 001 みかん銀行本店
03 田中 0002 001 りんご銀行長野支店
03 田中 0002 001 いちご銀行本店
15行が選択されました。

/* bank_cdのみWHERE句指定 */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s,bank_tb b
WHERE s.bank_cd = b.bank_cd /* ← 追加 */
ORDER BY 1
;

01 山田 0001 002 りんご銀行長野支店
01 山田 0001 002 りんご銀行本店
01 山田 0001 002 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行本店
02 鈴木 0001 003 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行長野支店
03 田中 0002 001 みかん銀行本店
7行が選択されました。

/* bank_cdとbranch_cdにWHERE句指定 */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s,bank_tb b
WHERE s.bank_cd = b.bank_cd /* 追加 */
AND s.branch_cd = b.branch_cd /* 追加 */
ORDER BY 1
;

01 山田 0001 002 りんご銀行青森支店
02 鈴木 0001 003 りんご銀行長野支店
03 田中 0002 001 みかん銀行本店

と、こんな感じになります。

Oracleだと、where句ですべて書いて行く書き方が出来ますので、完全な直積が発生します。通常の書き方だと、わざわざ"CROSS JOIN" と指定することになります。

SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s
CROSS JOIN bank_tb b
ORDER BY 1
;

ちなみに、以下の2つの書き方の、結果は同じです。

/* INNERT JOIN */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s
INNER JOIN bank_tb b ON s.bank_cd = b.bank_cd
AND s.branch_cd = b.branch_cd
ORDER BY 1
;

/* CROSS JOIN した上で、WHERE */
SELECT s.syain_id,s.syain_name,s.bank_cd,s.branch_cd,branch_name
FROM syain_tb s
CROSS JOIN bank_tb b
WHERE s.bank_cd = b.bank_cd
AND s.branch_cd = b.branch_cd
ORDER BY 1
;

最後の書き方をする方はいないと思いますが、こう書くと、Oracleと似ていますね。