読者です 読者をやめる 読者になる 読者になる

NULLIF 関数の実用的な使い方

SQL

NULLIF 関数って動作の説明は多いけど、それを何に使えばいいのかってあんまり理解されてないんじゃないかと思う。
ってことで、NULLIF 関数の実用的な使い方をいくつか紹介する。

NULLIF 関数の動作

まずは、NULLIF 関数の動作から。
NULLIF 関数は引数を 2 つとって、引数が等しい場合に NULL を返し、異なる場合に第一引数を返すだけの、動作自体はかなり簡単な関数。
例えば、

SELECT col_a, col_b, NULLIF(col_a, col_b) FROM SomeTable;

これを実行すると、次のような結果が得られる。

col_a col_b NULLIF(col_a, col_b)
10 10 NULL
10 0 10
0 10 0
NULL NULL NULL


また、プログラマのためのSQL 第2版では、CASE 式を用いて、

NULLIF(V1, V2) := CASE
                  WHEN (V1 = V2)
                    THEN NULL
                    ELSE V1
                  END;
プログラマのためのSQL 第2版 (P.50)

と同じと説明している。
更に、同書ではこの関数の特性として、

  1. NULLIF(x, x) は、すべての x の値に対して、NULL を返します。(NULL = NULL) は、UNKNOWN になり、TRUE ではありませんが、NULL の場合も NULL を返します。
  2. NULLIF(0, (x - x)) は、NULL ではないすべての x の値を NULL に変換します。しかし NULL は、0 に変換されます。なぜなら、(NULL - NULL) は NULL になり、等値性のテストに失敗するからです。
  3. NULLIF(1, (x - x + 1)) は、NULL でないすべての x の値を NULL に変換します。しかし NULL は 1 に変換されます。これは、すべての数値データ型と、値に関して一般化することができます。
プログラマのためのSQL 第2版 (P.50)

としている。


で、問題は、この関数は一体何に使えるのか、ということ。

ゼロ除算を避ける

おそらく NULLIF 関数の一番有名かつこれ以外の使い方は知られてないんじゃないか、と思うくらい有名なものがこれ。
ゼロ除算はエラーになってしまうので、ゼロを NULL に変換することでエラーを避け、結果を NULL にしてしまう、というもの。

SELECT
    col_a / NULLIF(col_b, 0)
FROM
    SomeTable

基本形はこれだけど、結果が NULL だと嬉しくない場合も多いので、COALESCE 関数と併用することも多い。

SELECT
    -- col_bが0の場合、0になる
    -- COALESCE関数の第二引数を変更することで、-1とかにもできる
    COALESCE(col_a / NULLIF(col_b, 0), 0)
FROM
    SomeTable

何かの比率を求める、ってのはよくあること*1なので、多分最も多く使う形。
これを CASE 式で書こうとすると、

SELECT
    CASE col_b
      WHEN 0 THEN 0
             ELSE col_a / col_b
    END
FROM
    SomeTable

長い・・・

無効値の NULL 化

NULL の使用を嫌って*2、NOT NULL を使っていると、無効値に 0 だとか -1 だとか、そういう値を用いることがある。
そして、それら無効値を NULL 化することで、COALESCE 関数を使って計算から無効値を取り除くことが出来る。
例えば -1 が無効値の場合、

SELECT
    COALESCE(NULLIF(col_a, -1), 1) * col_b
  , COALESCE(NULLIF(col_a, -1), 0) + col_c
FROM
    SomeTable

このように記述することで、計算に影響を与えない形に変換できる。
これを CASE 式で記述すると、

SELECT
    CASE col_a
      WHEN -1 THEN col_b
              ELSE col_a * col_b
    END
  , CASE col_a
      WHEN -1 THEN col_c
              ELSE col_a + col_c
    END
FROM
    SomeTable

やっぱり長い・・・

AVG や MIN の対象から除外する

これは使っている RDBMS にもよりそうな話なので、一応 SQL Server のみを対象としておく。

SELECT
    -- 0を集計対象外にしている
    -- 無効値を集計対象外にすることも容易
    AVG(NULLIF(col, 0))
  , MIN(NULLIF(col, 0))
FROM
    SomeTable

こうすると、0 の値を集計対象外に出来る。
まぁ、この場合、

SELECT
    AVG(col)
  , MIN(col)
FROM
    SomeTable
WHERE
    col <> 0

こうすればいいだけの話だけど、SQL Server では NULLIF を使ったほうがほんの少し高速になるケースが多いようだ*3

プログラマのためのSQL 第2版で紹介されている例

52 ページから 53 ページで紹介されている例だけど、一部分しか載ってない上、一部間違っている・・・

SELECT
    custno
  , COALESCE(
      NULLIF(COALESCE(NULLIF(0, P.dec - P.dec), 'Dec.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.nov - P.nov), 'Nov.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.oct - P.oct), 'Oct.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.sep - P.sep), 'Sep.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.aug - P.aug), 'Aug.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.jul - P.jul), 'Jul.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.jun - P.jun), 'Jun.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.may - P.may), 'May.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.apr - P.apr), 'Apr.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.mar - P.mar), 'Mar.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.feb - P.feb), 'Feb.'), 0)
    , NULLIF(COALESCE(NULLIF(0, P.jan - P.jan), 'Jan.'), 0)
    ) AS MonthOfLastPayment
FROM
    Payments P

この SQL で、顧客 No と顧客が最後に支払った月を取得できる*4
これを CASE 式を使って書くと・・・

SELECT
    custno
  , COALESCE(
      CASE
      WHEN P.dec IS NOT NULL
        THEN 'Dec.'
        ELSE NULL
      END
    , CASE
      WHEN P.nov IS NOT NULL
        THEN 'Nov.'
        ELSE NULL
      END
    , CASE
      WHEN P.oct IS NOT NULL
        THEN 'Oct.'
        ELSE NULL
      END
    , CASE
      WHEN P.sep IS NOT NULL
        THEN 'Sep.'
        ELSE NULL
      END
    , CASE
      WHEN P.aug IS NOT NULL
        THEN 'Aug.'
        ELSE NULL
      END
    , CASE
      WHEN P.jul IS NOT NULL
        THEN 'Jul.'
        ELSE NULL
      END
    , CASE
      WHEN P.jun IS NOT NULL
        THEN 'Jun.'
        ELSE NULL
      END
    , CASE
      WHEN P.may IS NOT NULL
        THEN 'May.'
        ELSE NULL
      END
    , CASE
      WHEN P.apr IS NOT NULL
        THEN 'Apr.'
        ELSE NULL
      END
    , CASE
      WHEN P.mar IS NOT NULL
        THEN 'Mar.'
        ELSE NULL
      END
    , CASE
      WHEN P.feb IS NOT NULL
        THEN 'Feb.'
        ELSE NULL
      END
    , CASE
      WHEN P.jan IS NOT NULL
        THEN 'Jan.'
        ELSE NULL
      END
    ) AS MonthOfLastPayment
FROM
    Payments P

もはや人間が書くものじゃない。

まとめ

NULLIF 関数は COALESCE 関数や極値関数、場合によっては CASE 式と合わせて使うことで、SQL を単純にできる。

*1:利益率だとか、成長率だとか・・・

*2:いいことだ

*3:といっても、48:52 といった微々たる差だけど

*4:ただ、年を固定してないからまだ問題あるけど・・・