NULL可のカラムを使って結合する(SYS_OP_MAP_NONNULL)

NOT NULLのカラムだけではなく、NULL可のカラムも使って結合したい場合がある。
どんな場合かというと、監査・現新比較などの時にそれをしたくなる。
キーだけではなく属性まで含めて FULL OUTER JOIN して、右 OR 左しかデータがないものは不一致として検知する時に使う。

全て NOT NULL の場合は、以下で良い。

SELECT * FROM T1
FULL OUTER JOIN T2
   ON T1.KEY1 = T2.KEY1
  AND T1.ATTR1 = T2.ATTR1
WHERE 
   T1.KEY1 IS NULL
   OR
   T2.KEY1 IS NULL
;

NOT NULL である KEY1 が NULL になるってことは、片割れしかないってことになる。
上の例で、 ATTR1 が NULL 可だと面倒になる。

NVL(T1.ATTR1, '存在しない値') = NVL(T2.ATTR1, '存在しない値')

みたいにやれば出来ないことは無いが、カラム毎にどんな値にしたらいいかを考えないといけないし、
ひょっとしたら想定外で同じ値があるかもしれない。

そこらを良いように処理してくれる関数が、SYS_OP_MAP_NONNULL である。

SELECT * FROM T1
FULL OUTER JOIN T2
   ON T1.KEY1 = T2.KEY1
  AND SYS_OP_MAP_NONNULL(T1.ATTR1) = SYS_OP_MAP_NONNULL(T2.ATTR1)
WHERE 
   T1.KEY1 IS NULL
   OR
   T2.KEY1 IS NULL
;

とするだけで終わり。型が何かを意識する必要もない。NULLを他とぶつからない値に変換してくれるので結合できる。
標準関数ではないので、マニュアルに載っておらず名前もNVLみたいにパッと出てくるものでは無いので、
いつもあの関数の名前なんだっけ？と悩むから備忘がてら書いてみました。

NULL可の項目で、NULLもインデックスで検索したいって時に SYS_OP_MAP_NONNULL を使ってファンクションインデックスを作るっていう利用法もある。