直積演算でSQLを理解する(2)

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

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

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

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 )


コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA