Oracleの外部結合(+)を用いたSQLを、通常のSQLで書き直したい。

Question

前提・実現したいこと

現在、Oracleに向けているシステムを、PostgreSQL用に設計し直すという作業をしています。
そのため、システムが発行しているSQLクエリを、PostgreSQLに書き直しているのですが、どうすれば良いのか分からないSQLがあり、それについて質問させていただければと思います。

発生している問題

以下のようなOracleの外部結合(+)を使って、色々と結合させているSQLがあります。

select
	a.column_1 as DATA01
	b.column_3 as DATA02
	c.column_7 as DATA03
	d.column_2 as DATA04
from
	AA as a,
	BB as b,
	(select .... from CC) as c,
	(select .... from DD) as d
where
	a.column_3 = c.column_3(+)
	a.column_4 = c.column_4(+)
	, b.column_3 = c.column_3(+)
	, c.column_3 = d.column_3(+)
	, c.column_4 = d.column_4(+)
	, c.column_6 < 21

このSQLは、このように、from句で副問合せが使われたテーブルを、外部結合で何度も利用しています（「c」とエイリアスが付けられた副問合せが、where句で、いくつも結合に使用されている事を意味します）。

これをPostgreSQLなどで動く、普通の外部結合（LEFT OUTER JOIN）を使って書き直すには、どうすれば良いのか、分かりますでしょうか？

試したこと

一応、以下のように書いてみましたが、（案の定というか当たり前ですが）同じエイリアスは設定できないと怒られてしまいます。

select
	a.column_1 as DATA01
	b.column_3 as DATA02
	c.column_7 as DATA03
	d.column_2 as DATA04
from
	AA as a LEFT OUTER JOIN (select .... from CC) as c on a.column_3 = c.column_3
	LEFT OUTER JOIN (select .... from CC) as c on a.column_3 = c.column_3
	, BB as b LEFT OUTER JOIN (select .... from CC) as c on b.column_3 = c.column_3
	......
	......
	......
where
	c.column_6 < 21

以下のように、それぞれエイリアスを異なる名前にすると、エラーは起きなくなりますが、やはり出力されるデータは、元のSQLと異なってしまいますよね？

select
	a.column_1 as DATA01
	b.column_3 as DATA02
	c.column_7 as DATA03
	d.column_2 as DATA04
from
	AA as a LEFT OUTER JOIN (select .... from CC) as c_01 on a.column_3 = c_01.column_3
	LEFT OUTER JOIN (select .... from CC) as c_02 on a.column_3 = c_02.column_3
	, BB as b LEFT OUTER JOIN (select .... from CC) as c_03 on b.column_3 = c_03.column_3
	......
	......
	......
where
	c_01.column_6 < 21

出力結果をOracleのときと同じにするために、どうにか、「エイリアスを設定している副問合せ」を、「c」のエイリアスは維持したまま、使い回せるような書き方をしたいのですが、うまいSQLが思いつきません。
何か案はありますでしょうか？

Comments

[Orlofsky]

質問にCREATE TABLE も追記した方が適切なコメントが付き易いです。

[kutunesirka]

ちょっと考えてみたのですが、大本のSQL自体が300行くらいある代物で、その一部を抜き出す形で質問させていただいたので、ちょっとCREATE文を失念していました。
今後、質問するときは、その辺りも気をつけてみようと思います。
ご指摘、ありがとうございます。

Answer 1

皆様、ありがとうございます。
色々とSQLで調査していた所、WITHを使うと思ったとおりのSQLが書けました。
（テストもしてみた所、出力結果は、Oracleの時と同様に出力されました）

WITH
    a as (select * from AA)
    , b as (select * from BB)
    , c as (select .... from CC)
    , d as (select .... from DD)
select
    a.column_1 as DATA01
    b.column_3 as DATA02
    c.column_7 as DATA03
    d.column_2 as DATA04
from
    a LEFT OUTER JOIN c on a.column_3 = c.column_3 and a.column_4 = c.column_4
    LEFT OUTER JOIN c on b.column_3 = c.column_3
    LEFT OUTER JOIN d on c.column_3 = d.column_3 and c.column_4 = d.column_4
where
    c.column_6 < 21

こいつで、一旦は進めていこうと思います。ありがとうございました。
（結合の2行目の「LEFT OUTER JOIN c on b.column_3 = c.column_3」は、ひょっとしたら「LEFT OUTER JOIN b on c.column_3 = b.column_3」とすべきなのかもしれませんが）

Comments

[sazi]

withはSQLはを括ることができて、可読性やレスポンスを高めます。
ですが、関係性において一筆書きするのと変わりはありません。
ですので、「withにしたからできた」ではありませんので、お間違えの無きよう。

[sazi]

where c.column_6 < 21
の部分でcは外部結合なので、nullが発生しますが除外されます。
結果、等結合と同じになります。
「見直しを」という理由の一部です。

[sazi]

a LEFT OUTER JOIN c on a.column_3 = c.column_3
LEFT OUTER JOIN c on a.column_3 = c.column_3
LEFT OUTER JOIN c on b.column_3 = c.column_3

上記のCはas C1とかでエイリアス付けてますよね？
というか、そうしないとエラーのはずですし

[kutunesirka]

> ですので、「withにしたからできた」ではありませんので、お間違えの無きよう。
withについても、指摘いただいた内容で理解しております。

> cは外部結合なので、nullが発生しますが除外されます。
where c.column_6 < 21の部分は、あまり考えずに（元のSQLに、似たような感じで絞り込み条件があったので）追加しています。基本的に、この部分は残しておくつもりです（今回の改修では、SQLは可能な限り、そのまま残すのが要件となっていますので）。

> 上記のCはas C1とかでエイリアス付けてますよね？
はい、エイリアスは付けております。つけるのを忘れておりました。

なお、テストを繰り返してみると、左と右のテーブルが同じ場合は、「a LEFT OUTER JOIN c on a.column_3 = c.column_3 and a.column_4 = c.column_4」というように、andで結んでやる必要があったようですので、今回はエイリアスがそもそも不要でした。

ご指摘、ありがとうございました。
↑のSQLも、そのように修正しております。

Answer 2

完全ではないですが、こんなイメージで。

select
    a.column_1 as DATA01
    b.column_3 as DATA02
    c.column_7 as DATA03
    d.column_2 as DATA04
from
    AA as a
join BB as b on a.～～ = b.～～ and ～～～
left join (
    select .... from CC
) as c on a.column_3 = c.column_3 and a.column_4 = c.column_4
left join (
    select .... from DD
) as d on ～～～～
where

該当行がないことが起こりうるのがCとdということであれば、
aやbのカラムとからめていかないとけなくて、
cとdのカラムで結合させようとすると破綻します。
aやbとc、aやbとdの組み合わせで見直ししてください。

Comments

[kutunesirka]

OracleのSQLをトレースするようなイメージで組んだ方が安全かなと思いましたが、やはり細かく出力結果を見ながら、結合させていったほうが良い感じなんでしょうかね？
いろいろと試してみます。ありがとうございます。

Answer 3

元のSQLの関係を再現すると以下になります。
※bとcの関係は確認した方が良いですね。
追記＆修正
c.column_6 < 21は結合条件ではないような。
だとするとCの抽出に含めた方が関係は崩さないですみそうですけど。
※結果が変わる（取得が多くなる）ならそもそも外部結合じゃなかったということで

select
    a.column_1 as DATA01
    b.column_3 as DATA02
    c.column_7 as DATA03
    d.column_2 as DATA04
from
    AA as a
    left join (select .... from CC where column_6 < 21) as c
    on a.column_3 = c.column_3 and a.column_4 = c.column_4
    left join (select .... from DD) as d
    on c.column_3 = d.column_3 and c.column_4 = d.column_4 
    right join BB as b
    on b.column_3 = c.column_3

Comments

[退会済みユーザー]

saziさん、はやぃ～

[sazi]

早いだけでしたー

[kutunesirka]

ありがとうございます。
やはり完全にOracleのSQLをトレースするようなイメージではなく、細かく出力結果を見ながら、結合させていったほうが良い感じなんでしょうかね？

いろいろと試してみます。

[sazi]

構文の解釈という意味では実現できていると思うんですけど、リレーション自体が「本当に？」みたいな印象です。
本来の要件に立ち返って見直すべきSQLだと思いますよ。
そうすれば無駄な結合（本来は等結合で良い部分とか）がなくなれば高速にもなるでしょうし。

[kutunesirka]

ご指摘、ありがとうございます。
確かに、作られたのが10年近く前のかなり古いシステムなので、テーブル構成などがかなり凄まじい作りになっています。
なので、時間があれば見直すべきなのでしょうが、今回の要件は、あくまでOracleからPostgreSQLの移行だけなので、テーブル構成は変えずに進めていっております。

いろいろと調べた所、WITHを使うと、上手くいきそうだったので、それで進めてみようと思います。

[Orlofsky]

10年くらい前ですと、FROMにはテーブルまたはビューを1つしか記述してはならない、SELECTの並びには * 以外を記述してはならない、ってSQL入門レベルのスキルがない人が作ったコーディング規約を厳守させていた大手SIerがありました。

[sazi]

join禁止という規約で揉めた事があります。
理由は条件が纏まっていて見やすいという事でした。

[Orlofsky]

Oracleに関してなら、
9.2 の最終パッチで JOIN のバグが解消できたかどうかでしたから、JOIN 禁止が妥当ですが、10g 以降で JOIN を使いたくないという人には正規マニュアルをきちんと読ませて叩き直すか、叩き出すかのどちらかで。

[kutunesirka]

もう10年くらい前のシステムなので、当初はまだjoin禁止だったのかもしれません……と思って色々と調べてみると、普通にjoinを使ってる箇所もあり、かと思うと内部結合もWHERで書いてる箇所もあり、どうもその辺りが統一されずに書かれたシステムのようです。

とりあえず、PostgreSQLに向けるとエラーを起こす箇所を修正するのが、今回の要件なので、普通に動いてる箇所はそのままにしておきます（無論、テストはしますが）。