直積演算で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 )