直積演算で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 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.idt2.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 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行が選択されました。