SQLの条件指定の有無を、変数やDB項目の値によって切り替える

最近会社で聞かれて答えたことをまとめてみました。

以下のようなことを聞かれました。

OracleのPL/SQLプログラムがある。0もしくは1となる変数があり、その変数の値によって抽出SQL(カーソル)の挙動を切り替えたい。
具体的には、変数の値が0なら従来SQLのまま、変数の値が1なら従来SQLに抽出条件を追加したSQLとしたい。
両者の条件はちょっとした違いなので、動的SQLにする方法や複数のカーソルを定義して変数の値によって切り替えるといった方法は避けたい。
動的SQLを使うことなく、単一のSQLの中で、変数の値に応じて抽出条件の加除はできるか。

このことについて、答えたこと、答えていないことをまとめてみました。

問題の置き換え

もともとの問題は、「Oracle PL/SQLプログラムの変数に応じて抽出SQLの条件指定有無を切り替える」ことでした。
しかし、「抽出SQLのある抽出項目Xの値に応じて、抽出項目Yを用いた抽出条件の有無を切り替える」と考えることもできます。

条件追加の判断に用いるものが変数なのかDB項目なのか、その違いだけだからです。
そして、このように問題を置き換えることで、Oracleに限定せずSQL全般に問題を一般化できます。

実験環境

ここの説明でよく使っている"Oracle 11g XE"を使ってみます。

実験および解説

条件追加

従来SQL

以下の2項目からなるデータ抽出SQLを考えます。

• 項目X : 0 or 1の値を取る整数値
• 項目Y : 1 ～ 10の値を取る整数値

そして、項目Yが2で割り切れるものだけを抽出するSQLを考えます。すると、以下のようなSQLになります。

WITH
TBL_X AS ( SELECT ROWNUM - 1 AS X FROM ALL_OBJECTS WHERE ROWNUM <= 2 )
,
TBL_Y AS ( SELECT ROWNUM AS Y FROM ALL_OBJECTS WHERE ROWNUM <= 10 )

SELECT  TBL_X.X
    ,   TBL_Y.Y
FROM    TBL_X CROSS JOIN TBL_Y
WHERE   MOD ( TBL_Y.Y, 2 ) = 0
ORDER BY
        TBL_X.X
    ,   TBL_Y.Y

抽出結果は以下のようになります。

X	Y
0	2
0	4
0	6
0	8
0	10
1	2
1	4
1	6
1	8
1	10

項目Xの値によって抽出条件の追加を行うSQL その1

項目X=1なら、さらに「項目Yが3でも割り切れる」という条件追加を行うことを考えます。すると、以下のようなSQLになります。

WITH
TBL_X AS ( SELECT ROWNUM - 1 AS X FROM ALL_OBJECTS WHERE ROWNUM <= 2 )
,
TBL_Y AS ( SELECT ROWNUM AS Y FROM ALL_OBJECTS WHERE ROWNUM <= 10 )

SELECT  X
    ,   Y
FROM    TBL_X CROSS JOIN TBL_Y
WHERE   MOD ( Y, 2 ) = 0
    AND (
                ( ( X = 0 )                          )
            OR  ( ( X = 1 ) AND ( MOD ( Y, 3 ) = 0 ) )
        )
ORDER BY
        X
    ,   Y

抽出結果は以下のようになります。

X	Y
0	2
0	4
0	6
0	8
0	10
1	6

項目X=0の部分には、項目Yが2で割り切れるものが抽出されています。項目X=1の部分には、項目Yが2でも3でも割り切れるものが抽出されています。項目Xの値が0と1のどちらであるかによって、項目Yの抽出条件指定を切り替えることができています。

解説

今回のケースで、以下に挙げる各条件を同等のものと見なせるかどうかが鍵です。

• 「項目X=1」なら「項目Yが3で割り切れる」という追加条件を加える。
• 「項目X=0」なら追加(絞り込み)条件なし、「項目X=1」なら「項目Yが3で割り切れる」という追加(絞り込み)条件を加える。
• 「項目X=0」か、「項目X=1」で「項目Yが3で割り切れる」か、どちらかを満たすデータなら抽出可能
• ( X = 0 ) OR ( ( X = 1 ) AND ( MOD ( Y, 3 ) = 0 ) )

項目Xの値によって抽出条件の追加を行うSQL その2

会社で説明したSQLは、以下のような抽出条件でした。前項の抽出条件とは指定の仕方が異なります。

WITH
TBL_X AS ( SELECT ROWNUM - 1 AS X FROM ALL_OBJECTS WHERE ROWNUM <= 2 )
,
TBL_Y AS ( SELECT ROWNUM AS Y FROM ALL_OBJECTS WHERE ROWNUM <= 10 )

SELECT  X
    ,   Y
FROM    TBL_X CROSS JOIN TBL_Y
WHERE   MOD ( Y, 2 ) = 0
    AND ( ( X = 0 ) OR ( MOD ( Y, 3 ) = 0 ) )
ORDER BY
        X
    ,   Y

抽出結果は以下のようになります。前項と同じ結果になっていることが分かります。

X	Y
0	2
0	4
0	6
0	8
0	10
1	6

解説

「項目Xの値によって抽出条件の追加を行うSQL」と題して書いたSQLのうち、差異部分を抜き出してみます。

前者の条件はこうです。

    AND (
                ( ( X = 0 )                          )
            OR  ( ( X = 1 ) AND ( MOD ( Y, 3 ) = 0 ) )
        )

後者の条件はこうです。

    AND ( ( X = 0 ) OR ( MOD ( Y, 3 ) = 0 ) )

この二つが同じものを指しているということを説明しましたが、すんなりと受け入れられませんでした。そのため、改めて説明してみることにします。

『プログラマの数学』の第二章には、カルノー図が紹介されています。この両者をカルノー図を交えて説明します。

カルノー図は、複数の真偽の組み合わせを表の形で表したものです。今回であれば「X=0」「X=1」「MOD ( Y, 3 ) = 0」が真偽として登場します。但し、「X=0」と「X=1」は逆の関係ですので、まとめることができます。結果、「X=0」「MOD ( Y, 3 ) = 0」をカルノー図で表すことにします。

前者の条件をカルノー図で表すと、以下のようになります。

後者の条件をカルノー図で表すと、以下のようになります。

青色の囲みは「右半分」で共通です。前者と後者の違いは、赤色の囲みに集約されます。

前者の赤色の囲みは、青色の囲みと重ならないように気を配って、「左下」となっています。
後者の赤色の囲みは、青色の囲みと重なることを気にせず、「下半分」となっています。

前者の赤色の囲み「左下」を条件指定するためには、「左半分」かつ「下半分」という指定を行う必要があります。複数条件のAND条件となるわけです。
他方、後者の赤色の囲み「下半分」を条件指定するためには、「下半分」という指定だけで事足ります。単一条件となるわけです。
そのため、後者のほうがよりシンプルな条件指定となるわけです。

複数の条件が重なってもいいから、できるだけ大きなかたまりを選ぶ。そのことによって、論理条件をシンプルに表現できる。そのようなことが『プログラマの数学』では説明されています。

複雑なものをよりシンプルに表現する。これは技術者に必要な技量の一つといえると思います。こういったところを学習すると、今回挙げた2種類のSQLが同じであるということもすんなりと受け入れられるのではないでしょうか。

条件除去

前項では、条件追加を説明しました。条件を再掲します。

• 従来：「項目Yが2で割り切れる」
• 修正後：「項目Yが2で割り切れる」ことに加えて、項目X=1の場合には「項目Yが3で割り切れる」という条件を追加

既存条件から条件除去する場合も同様に考えることができます。

• 従来：「項目Yが2で割り切れる」かつ「項目Yが3で割り切れる」
• 修正後：「項目Yが2で割り切れる」条件はそのままだが、項目X=0の場合には「項目Yが3で割り切れる」という条件を除去

条件追加の修正後で抽出されるものと、条件除去の修正後で抽出されるものが同じものとなることは明らかです。そのため、例題は省略します。

但し、基本の考え方や条件をシンプルにするための考え方は同じです。

PL/SQLの変数とDB値の関係

この記事の冒頭、「問題の置き換え」で、条件加除の判断を「PL/SQLプログラムの変数」ではなく「抽出項目」に置き換えて考えていました。

この記事の途中、「項目Xの値によって抽出条件の追加を行うSQL その1」の解説部分で、以下の解説があります。

「項目X=0」か、「項目X=1」で「項目Yが3で割り切れる」か、どちらかを満たすデータなら抽出可能

どちらかを満たす「データ」とありますが、抽出可否を判断する条件のベースとなる値が「データ」である必要はないのです。「PL/SQL変数」でも構わないのです。

• 「項目X=0」か、「項目X=1」で「項目Yが3で割り切れる」か、どちらかを満たすデータなら抽出可能
• 「項目X=0」か、「項目X=1」で「項目Yが3で割り切れる」か、どちらかを満たすなら抽出可能
• 「PL/SQL変数X=0」か、「PL/SQL変数X=1」で「項目Yが3で割り切れる」か、どちらかを満たすなら抽出可能

このように置き換えても、同じ手法で条件指定有無を切り替えることができる。それを理解すれば、もともとの問題「PL/SQLプログラムの変数に応じて抽出SQLの条件指定有無を切り替える」が可能だということも、改めて理解できるでしょう。

まとめ

この記事では、以下のことを説明しました。

• SQLのWHERE句を変えるだけで条件指定の加除を行うことができる。
• 複数の真偽条件の組み合わせをよりシンプルな表現で表せる場合がある。
• SQLの抽出条件指定を行う際に、PL/SQL変数を用いることもできる。

いずれの要素もかなり基本的なことです。そのため、感覚的に理解できてしまうことも多く、学習がおろそかになりがちです。

これまで私は、カルノー図を無意識下で使っていました。どこかで見たことがあるかもしれず、どこかで名前を聞いたことがあるかもしれず、しかしそのようなことを忘れて使っていました。最近『プログラマの数学』を読み、カルノー図を学習しなおしたことで改めて、「論理条件をシンプルに表現」するための仕組みだということ、それを無意識に使っていたのだと理解しました。

人が覚える方法にはいろいろあり、理論を理解してから実践で使えるという人、実践で使ううちに覚えて理論が後からついていく人、いろいろな人がいるだろうと思っています、しかし100%理論とか100%実践とかいう人はいなくて、理論と実践が補完しあうのだろうと思っています。

今回この記事をまとめることで、カルノー図の「理論」部分を補うことができてよかったと思っています。