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


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


 


 
例えば以下のような2つのテーブルがあったとする
■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)  
)  
/  


それぞれ以下のデータが入ってるとする。(★はKEY)
■TEST_TAB1

★TEST_NOTEST_STR

1 テストです。1
2 テストです。2
3 テストです。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 テストです。1 3
2 テストです。2 3
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 テストです。1 テスト 太郎
2 テストです。2 テスト 次郎
3 テストです。3 (null)


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




ただ、このやり方は、単一項目しか取得できないうえ、SQLの都合上、必ず1レコードで返ってこないとエラーになる。
例えば以下のようなテーブル「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の1番です。
1 2 テスト1の2番です。


要するに「TEST_NO = '1'」に該当するレコードが、
TEST_TAB1は(主KEYだし)1レコードで特定できるが、
TEST_TAB2は2レコードになって、「TEST_NO」だけではレコードが特定できない(このため、意図的に主KEYを外した)
このため以下のようなSQLは実行エラーになる。

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つ以上の行が戻されます  


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

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は(とりあえず現状のデータに対しては)1レコードに限定できたという意味で問題なく動く。
勿論、この後、TEST_NO = '1' and TEST_SEQ = 1に該当するレコードが2レコード以上発生すると、
同様のORACLEエラーが発生してSQLは実行失敗する。

逆に言うと、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を副問い合わせにいれる)に似ているが、
集計関数を使えば、関数の仕様上結果が勝手に1レコードになってくれるので、これでも問題ない
(望むものが取得できるかの保証はないが…)




余談だがこのエラーは運用してからもたまに見る。
テーブルの主KEYと、業務的に使う条件が一致してないときに起こりうる。(そりゃそうだ)
実体験でも実際、遭遇したことがある。
→”普通は”、「受注NO」だけで一意に特定できる、とか言ってたからSQLはその通り実装したんだけど
 テーブルは「連番シークエンス」みたいのをKEYにしてて、
 でも通常は「受注NO」だけで一意になるから普段は困らないんだけど、
 非常に稀なケースで同一受注NOの別レコードが発生したりして、
 テーブルはKEYじゃないから入るのだが、SQLで落ちる、みたいな…

実際には、こういった実装ではなく、
PL/SQLの中で、取得項目を変数に格納する(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)のKEYが完全一致していることの確認は必須。
なので、この例でいえば、
TEST_TAB1からTEST_TAB2を参照するのはまあやってもいいと思う(両テーブルとも「TEST_NO」だけがKEYなので)が
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  


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



ちなみに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句に書いてあるSQLも、これ単体では動作しないが、
「TEST_TAB1とTEST_TAB2を内部結合して、両者に存在するTEST_TAB1のレコードのみ抽出せよ」という指示になっている。
これ自体は「存在チェック」として働かせるもの(目的が「項目の取得」ではない)なので、
TEST_TAB3みたいに主KEYがないテーブル相手でもエラーなく動作する。