直積演算でSQLを理解する

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と似ていますね。