【Oracle】副問い合わせに条件を適用して項目を取得するやり方

IT

SELECTやUPDATEの一部に部分的に副問い合わせ（インラインビュー）を組み込む方法について。

 

 
例えば以下のような２つのテーブルがあったとする
■TEST_TAB1

create table TEST_TAB1(  
    TEST_NO char(10) PRIMARY KEY  
   ,TEST_STR varchar2(100)  
)  
/


■TEST_TAB2

create table TEST_TAB2(  
    TEST_NO char(10) PRIMARY KEY  
   ,TEST_NAME varchar2(50)  
   ,TEST_FLG char(1)  
)  
/


それぞれ以下のデータが入ってるとする。（★はＫＥＹ）
■TEST_TAB1

★TEST_NOTEST_STR

1 テストです。１
2 テストです。２
3 テストです。３


■TEST_TAB2

★TEST_NOTEST_NAMETEST_FLG

1 テスト　太郎 1
2 テスト　次郎 0
4 テスト　四朗 1

 


で、本懐である。

一番わかりやすい（？）のは

select  
  TEST_NO  
  ,TEST_STR  
  ,(select count(1) from TEST_TAB2) as TEST_TAB2_CNT  
from TEST_TAB1


みたいなやり方だろう。
結果はこうなる↓

TEST_NOTEST_STRTEST_TAB2_CNT

1 テストです。１ 3
2 テストです。２ 3
3 テストです。３ 3


基本的に「TEST_TAB1」からのデータSELECT文だが、SELECTと同時に別テーブル(TEST_TAB2)からの件数取得を行い、SELECT結果として取得してくる。

この例に限定して言えば、
この副問い合わせ(select count(1) from TEST_TAB2)自体が独立して動作するため、
わざわざ他項目の取得と一緒に実行する意味はまったくない。
（実行計画がどうなるのかは知らないが、必ず固定で同じ結果しか返さない別テーブルのCOUNTを、TEST_TAB1のレコード取得の度にやる意味はまったくない）

一方でこういう↓使い方もできる。

select  
  TEST_NO  
  ,TEST_STR  
  ,(select TEST_TAB2.TEST_NAME  
    from TEST_TAB2  
    where 0=0  
      and TEST_TAB2.TEST_NO = TEST_TAB1.TEST_NO  
    ) TEST_NAME  
from TEST_TAB1


結果はこうなる↓

TEST_NOTEST_STRTEST_NAME

1 テストです。１ テスト　太郎
2 テストです。２ テスト　次郎
3 テストです。３ (null)


この赤太字部分（副問い合わせ部分）は、最初の例と違って独立して動作しない。（ここだけ抜き取ってＳＱＬ実行してもエラーになる、という意味）
WHERE句にある「 and TEST_TAB2.TEST_NO = TEST_TAB1.TEST_NO」の部分がこのＳＱＬ単体では実行できないからである。
内容といえば、TEST_TAB1.TEST_NOとTEST_TAB2.TEST_NOを紐づけて、
該当する「TEST_TAB2.TEST_NAME」を取得してくるだけである。
ただし、TEST_NO = '3'に該当するレコードは、TEST_TAB1にはあるがTEST_TAB2にはないので、
↑のＳＱＬの実行結果のうち、TEST_NO = '3'の行のTEST_NAME項目はnullになっている。
つまり、実行内容としては、TEST_TAB1を基準としてTEST_TAB2を外部結合している形になる。
（当然だが、この「TEST_NAME」はTEST_TAB2にしか持っていない項目の前提で書いている）
基本的にはTEST_TAB1からのSELECTなのだが、
一部項目だけどうしてもTEST_TAB1には持っていない項目があるので、
そこだけ限定的にTEST_TAB2を参照して項目を持ってくる、という意図になっている。



ただ、このやり方は、単一項目しか取得できないうえ、ＳＱＬの都合上、必ず１レコードで返ってこないとエラーになる。
例えば以下のようなテーブル「TEST_TAB3」があって、

create table TEST_TAB3  
(   test_no char(10)  
   ,test_seq number(10)  
   ,test_memo varchar2(100)  
)  
/


下記のようなデータが入っているとする。

TEST_NOTEST_NUMBERTEST_MEMO

1 1 テスト１の１番です。
1 2 テスト１の２番です。


要するに「TEST_NO = '1'」に該当するレコードが、
TEST_TAB1は（主ＫＥＹだし）１レコードで特定できるが、
TEST_TAB2は２レコードになって、「TEST_NO」だけではレコードが特定できない（このため、意図的に主ＫＥＹを外した）
このため以下のようなＳＱＬは実行エラーになる。

select TEST_NO  
      ,TEST_STR  
      ,(select TEST_TAB3.TEST_MEMO  
        from TEST_TAB3  
        where TEST_TAB3.TEST_NO = TEST_TAB1.TEST_NO  
       ) TEST_MEMO  
from TEST_TAB1  
----------------------------------------------------------------------  
ORA-01427: 単一行副問合せにより2つ以上の行が戻されます


こんな感じ。
ただ、参照先のテーブルにＫＥＹがあろうがなかろうが、とりあえず１レコードで絞れればいいらしいので、
この例で言うと以下のようなＳＱＬならエラーなく実行できる。

select TEST_NO  
      ,TEST_STR  
      ,(select TEST_TAB3.TEST_MEMO  
        from TEST_TAB3  
        where TEST_TAB3.TEST_NO = TEST_TAB1.TEST_NO  
          and TEST_TAB3.TEST_SEQ = 1  
       ) TEST_MEMO  
from TEST_TAB1

赤太字を条件として追加することで、
TEST_TAB3は（とりあえず現状のデータに対しては）１レコードに限定できたという意味で問題なく動く。
勿論、この後、TEST_NO = '1' and TEST_SEQ = 1に該当するレコードが２レコード以上発生すると、
同様のＯＲＡＣＬＥエラーが発生してＳＱＬは実行失敗する。

逆に言うと、１レコードになってりゃいいので、↓みたいなやり方でもとりあえずエラーは回避できる。

select TEST_NO  
      ,TEST_STR  
      ,(select max(TEST_TAB3.TEST_MEMO)  
        from TEST_TAB3  
        where TEST_TAB3.TEST_NO = TEST_TAB1.TEST_NO  
       ) TEST_MEMO  
from TEST_TAB1

最初の例(COUNTを副問い合わせにいれる)に似ているが、
集計関数を使えば、関数の仕様上結果が勝手に１レコードになってくれるので、これでも問題ない
（望むものが取得できるかの保証はないが…）



余談だがこのエラーは運用してからもたまに見る。
テーブルの主ＫＥＹと、業務的に使う条件が一致してないときに起こりうる。（そりゃそうだ）
実体験でも実際、遭遇したことがある。
→”普通は”、「受注ＮＯ」だけで一意に特定できる、とか言ってたからＳＱＬはその通り実装したんだけど
　テーブルは「連番シークエンス」みたいのをＫＥＹにしてて、
　でも通常は「受注ＮＯ」だけで一意になるから普段は困らないんだけど、
　非常に稀なケースで同一受注ＮＯの別レコードが発生したりして、
　テーブルはＫＥＹじゃないから入るのだが、ＳＱＬで落ちる、みたいな…
実際には、こういった実装ではなく、
ＰＬ／ＳＱＬの中で、取得項目を変数に格納する（INTO句）ときに遭遇することが多い。

select TEST_MEMO  
  into test_memo_work  
from TEST_TAB3  
where TEST_TAB3.TEST_NO = :test_no_bind

↑みたいな感じか。


話を戻すと、このやり方は、
”単一レコード単一項目が取得できる”ことを前提とした実装になっているため、割と危ういというのが言いたいことなのである。
どうしてもやるなら、参照する側(TEST_TAB1)の、参照される側(TEST_TAB2,TEST_TAB3)に対して見ようとしている条件と、
参照される側(TEST_TAB2,TEST_TAB3)のＫＥＹが完全一致していることの確認は必須。
なので、この例でいえば、
TEST_TAB1からTEST_TAB2を参照するのはまあやってもいいと思う（両テーブルとも「TEST_NO」だけがＫＥＹなので）が
TEST_TAB1からTEST_TAB3を参照するのは、たとえ現在の条件でTEST_TAB3側が一意に特定できるとしてもやるべきではない。

というか、やってることは外部結合して項目取得してきてるだけなので、要するに

select TEST_TAB1.TEST_NO  
      ,TEST_TAB1.TEST_STR  
      ,TEST_TAB2.TEST_NAME  
from TEST_TAB1  
     left join TEST_TAB2  
            on TEST_TAB1.TEST_NO = TEST_TAB2.TEST_NO

とか

select TEST_TAB1.TEST_NO  
      ,TEST_TAB1.TEST_STR  
      ,TEST_TAB3.TEST_SEQ  
      ,TEST_TAB3.TEST_MEMO  
from TEST_TAB1  
     left join TEST_TAB3  
            on TEST_TAB1.TEST_NO = TEST_TAB3.TEST_NO


と、全く同じなのである。
むしろこっちの例の方が、複数項目取得できるうえエラーになる可能性もない。
なので、このやり方は、あえて実装する必要があるというとき（そんなときあるのか？）以外、採用するべきではないというのが個人的な感想である。
個人的には、調査用のＳＱＬとか、手元で軽く動かすときに組むときに、このやり方を使っている
（参照先のテーブルのわずか１項目だけがほしいときに、そのためだけに結合を増やしたくない（面倒）ときなんかに使う）


ちなみにSELECT項目の一部として↑のやり方を取るなら前述までの記述の通りだが、実際にはexists句に使ったりする方が多いと思う。

select TEST_TAB1.TEST_NO  
      ,TEST_TAB1.TEST_STR  

from TEST_TAB1

where exists (

select 1

from TEST_TAB2

where TEST_TAB2.TEST_NO = TEST_TAB1.TEST_NO

)

このexists句に書いてあるＳＱＬも、これ単体では動作しないが、
「TEST_TAB1とTEST_TAB2を内部結合して、両者に存在するTEST_TAB1のレコードのみ抽出せよ」という指示になっている。
これ自体は「存在チェック」として働かせるもの（目的が「項目の取得」ではない）なので、
TEST_TAB3みたいに主ＫＥＹがないテーブル相手でもエラーなく動作する。