外部結合で副問い合わせ

前回の続きです。[SQLのWHERE句での結合とINNER JOINでの結合の違い]

■table_header(親テーブル)

(PK)	mng_key
	cmn_a
	…

■table_detail(子テーブル)

(PK)	mng_key
(PK)	sub_key
	cmn_b
	…

■table_cmn

(PK)	surrogate_key
	cmn_a
	cmn_b
	cmn_c
	…

こんな感じの3つのテーブルがあり、親と子を内部結合し、3つ目のテーブルを外部結合したい場合、INNER JOIN＋LEFT OUTER JOINを使おうという結論になりました。

ですが、実はこの記事を書くときに困っていた事象というのは、もう一歩だけ踏み込んだ状況でした。

3つ目のテーブルを外部結合したい理由は、cmn_aやcmn_bがNULLの可能性があり、それにつられて親と子のテーブルがSELECT結果から消えるのを避けたいためです。

問題は、この外部結合で副問い合わせを使わざるを得ない場合です。

cmn_a	cmn_b	cmn_c
AA	10	…
AA	20	…
AA	30	取りたいカラム
BB	10	取りたいカラム
CC	20	取りたいカラム

すごい適当ですが、こんな感じのデータで、「cmn_a内に同値が複数ある場合はcmn_bが最大のレコードを結合し、cmn_cを取得したい」とかいう場合です。

前回の結果を踏襲しつつ、素直にSQLを書いたら以下のようになりました。

SELECT *
FROM table_header hed
  INNER JOIN table_detail dtl
    ON hed.mng_key = dtl.mng_key
  LEFT OUTER JOIN (
    SELECT *
    FROM table_cmn a
    WHERE cmn_b = (
      SELECT MAX(cmn_b) AS cmn_b
      FROM table_cmn b
      WHERE a.cmn_a = b.cmn_b
      GROUP BY cmn_a
    )
  ) cmn
    ON hed.cmn_a = cmn.cmn_a
   AND dtl.cmn_b = cmn_cmn_b

細部は間違えているかもしれません。この辺り、あとで修正しておきます。

ともかく、上記のように外部結合するテーブルを副問い合わせとした場合、「ORA-01799」となってしまいます。

この解決方法を考えましたが、全く思いつかず。

結局、性能を度外視して副問い合わせそのものをSELECT句に持っていくこととなりました。

たぶん、テーブル構成からして駄目なのだと思います。