SQLで日付範囲指定のある場合の結合方法について

Question

いつもお世話になっております。
Oracleを使用しているのですが、Webアプリで仕訳元帳の照会を行うことになりました。

仕訳データの中に入っている勘定科目と補助科目の科目名を表示したいのですが、
SQL文で、

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
WHERE a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日;

とした時、全ての勘定科目に補助科目があるとすれば問題ないと思うのですが、当然ながら、勘定科目の中には補助科目を持たないものも存在します。
このため、WHERE文で、補助科目の無い場合を想定して、下記のように書いていますが、WHERE文が複雑だと処理効率が悪すぎることになります。

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
WHERE (a.補助科目 is not NULL AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日) OR
(a.補助科目 is NULL AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日)

基本的なことで大変申し訳ないのですが、
こうした場合、LEFT JOIN の中に日付の結合条件を書けば同じことになるのでしょうか？

LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日

Comments

[Orlofsky]

質問にCREATE TABLE文、何件かデータのINSERT文と希望するSELECT結果も載せてください。

[hatsuzo]

コメントありがとうございます。
文が長くなるので敢えて表記しませんでしたが、
列名と表名は日本語表記、
日付の書き方は正しい表記ではないと思いますが、構造の判る範囲で
簡略化して書きました。

CREATE TABLE 仕訳 (
伝票No char(10) PRIMARY KEY,
行No number(2) PRIMARY KEY,
貸借 char(1),
計上日 date,
勘定科目CD varchar2(5),
勘定科目CD varchar2(5),
金額 date
);

CREATE TABLE 勘定科目 (
勘定科目CD varchar2(5) PRIMARY KEY,
有効開始日 date PRIMARY KEY,
有効終了日 date PRIMARY KEY,
勘定科目CD varchar2(30)
);

CREATE TABLE 補助科目 (
勘定科目CD varchar2(5) PRIMARY KEY,
補助科目CD varchar2(5) PRIMARY KEY,
有効開始日 date PRIMARY KEY,
有効終了日 date PRIMARY KEY,
補助科目名 varchar2(30)
);

仕訳

INSERT ALL
INTO 仕訳 VALUES('0000000001', 1, '1', 2019/05/28, '1111', '1', 1500)
INTO 仕訳 VALUES('0000000001', 1, '2', 2019/05/28, '4444', '', 1500)
SELECT * FROM DUAL;

勘定科目

INSERT ALL
INTO 勘定科目 VALUES('1111',1900/1/1,2999/12/31,'現金')
INTO 勘定科目 VALUES('1112',1900/1/1,2999/12/31,'普通預金')
INTO 勘定科目 VALUES('4444',1900/1/1,2018/12/31,'旅費')
INTO 勘定科目 VALUES('4444',2019/1/1,2999/12/31,'旅費交通費')
SELECT * FROM DUAL;

補助科目

INSERT ALL
INTO 補助科目 VALUES('1111','1', 1900/1/1,2019/9/30,'小口現金')
INTO 補助科目 VALUES('1111','1', 2019/10/1,2999/12/31,'小口現金(１)')
INTO 補助科目 VALUES('1112','1', 1900/1/1,2999/12/31,'三菱UFJ銀行')
SELECT * FROM DUAL;

結果はこんなイメージです。

'1111','現金', '1', '小口現金'
'4444','旅費交通費', '', ''

[Orlofsky]

[質問]に追記してください。

[Orlofsky]

PRIMARY KEYの書き方はきちんとしてください。
, CONSTRAINT PRIMARY_KEY_NAME1 PRIMARY KEY(COL1, COL2) USING INDEX

Answer 1

LEFT JOIN の中に日付の結合条件を書けば同じことになるのでしょうか？

同じ事というより外部結合の場合は結合条件に含めた方が記述はすっきりします。

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM  仕訳 a 
      LEFT JOIN 勘定科目 b 
      ON    a.勘定科目CD = b.勘定科目CD 
        AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
      LEFT JOIN 補助科目 c 
      ON    a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD 
        AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日

※有効終了日の条件は以下にならなくて良いのでしょうか？

Comments

[hatsuzo]

コメントありがとうございました。
結合条件で書いてしまえばよいのですね。今まで不効率な書き方をしてきたような気がします。（汗）
aとbの有効日の結合条件は、2つ目の結合では書く必要が無い、というか書いたら変ということですね。

[sazi]

クエリービルダーのようなGUIを使用すると、結合条件は=以外は表現できず、whereで記述するしかありませんので、そういうものだと思われている方はおられますね。

[hatsuzo]

お恥ずかしいですが、まさにその通りです。

Answer 2

そんな時には SELECT句のサブクエリはいかがでしょう。
こんな感じで

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, 
(
    SELECT TOP(1) c.補助科目名
    FROM 補助科目 c
    WHERE a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
      AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日
) 補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
WHERE a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日;

遅いときは WITH 句を使うといいです。
以下蛇足です。
財務系は古い環境が残っていることが多く、インデックスなども変更できないケースが多々あると存じます。
場合によっては、SELECT句にサブクエリを書くと(キャッシュが効かず)大きくパフォーマンスを落とす可能性があります。
そんな時に手軽にキャッシュを効かせやすくする手法の一つとしてWITH句を追加する方法があります。

WITH cache AS
(
	SELECT 勘定科目CD
		  ,補助科目CD
		  ,補助科目名
		  ,有効開始日
		  ,有効終了日
    FROM 補助科目
)

サブクエリの補助科目テーブルの代わりにcacheを使ってください。
ただし通常はインデックスが無駄になりますので有害です。

Comments

[sazi]

WITH式を使う要素は見当たらない気がしますが。

[hatsuzo]

コメントありがとうございます。

不勉強なもので、WITHというのは使ったことが無いのですが、副次問合せの部分を共通化して切り出す、ということなのでしょうか？

[hihijiji]

その通りです。
もちろん現状で十分なパフォーマンスが出ている場合は不要です。

[sazi]

@hihijijiさん
相関副問合せですし、TOP使ってますし、ここからwith式を抜き出すのは無理がありませんか？

[hihijiji]

WITH句は補助科目テーブルの必要項目だけを抜き出したキャッシュとして利用するだけで、
サブクエリの構文はほぼそのままです。
古いDBMSではSELECT句にサブクエリを書くとパフォーマンスが大きく落ちる場合があるので、割と一般的な使い方でした。
最近のDBMSではあまり関係ないので蛇足的に書きましたが、それ自体蛇足だったかもしれません。

[sazi]

@hihijijiさん
with使うことによるパフォーマンスについては、コメントしていません。
回答されているのは相関サブクエリーなので、withへ置き換えできないのでは？という事です。
手間でなければ、with使用したSQLを追記して貰えると助かります。

[sazi]

@hihijijiさん
追記、ありがとうございます。
そのcatchをサブクエリー内の補助科目に置き換えるという事ですね。