SQLで困りがちなnullをうまく避けられるcoalesceを使いこなす

データベースからデータを抽出した際、抽出結果にnullが出てくるのを防ぐために使われるのがcoalesceです。

nullの扱いは難しくありませんが、知識がないと値が表示されないなどのエラーの原因となります。今回はnullと共にcoalesceをサンプルのデータやSQLを使ってご紹介します。

coalesceとは何か

まずは、coalesceの意味と使い方をご説明します。

nullを変換するときに使われる

coalesceは入力された引数のうち、nullではない最初の引数を返します。引数にはデータの列名を指定して使います。

データにnullが入るケースはさまざまなものが想定されます。たとえば、レコード追加時にその列だけデータを入れなかったときや、テーブルに新規の列を1列追加したときなどに発生する場合があります。

数値なら初期値として0などを入力するとnullにはなりませんが、初期値をセットする工程や処理がないと、nullのままデータが作成されてしまいます。

coalesceはそのような過程でできてしまったnullを何かの値に置き換えるときに使います。置き換えるといってもデータを直接更新するわけではありません。あくまでもSQL発行時の結果表示を置き換えるのみのため、更新権限を持っていないデータベースのデータを抽出する際にも使えます。

nullを変換する理由

nullはデータが何も入っていない状態のことを指します。文字や数字が何も入っていない状態になるため、プログラムで条件分岐の引数に使われてしまうと処理エラーの原因となります。

またnullの分だけデータの種類が増えることになり、データの扱いを複雑にしてしまうことになりかねません。たとえば年齢でグループ分けして抽出する際、29歳以下と30歳以上の2グループに分けるだけでよかったデータが、nullの場合という第三のグループの検討を必要にしてしまいます。

プログラム側で初期値をセットしたり、nullを許容しない作りにしたり、何らかの対策は打たれているはずですが、抽出結果にもnullはない方がデータは取り扱いやすくなります。

coalesceの使い方

ではここからは、coalesceの使い方を、実際のサンプルコードを使ってご説明します。

よく使われるケース

抽出したデータを後続の処理や作業で計算する前に、初期値をセットするのによく使われます。数値なら0、文字列ならデフォルトで表示される値を何か決めて入れます。特に計算過程の途中にnullがあると、それ以降の計算結果がすべてnullになってしまうため、使うデータにnullがないことは重要になります。

複数の計算が続くような場合、どこでnullになったかを遡って探すのは負担であると同時に、大量のデータでnullが出てしまうとすべて探し出すのは現実的ではありません。先にnullのデータを置き換えておくとスムーズに計算が進められます。

coalesceを使ったSQLの書き方

以下のデータが入った「成績」表を例にとってSQLを書いてみましょう。

評価列の値にしたがって、一定の倍率のボーナスを付与する計算をしようとしています。今の状態で「評価」列の値を取得すると、鈴木剛規はnullのため値が存在せず、一定の倍率を設定することができません。そのためnullを何かに置き換える必要があります。

今回は評価列がnullの場合、一律で「F」に置き換えることとします。デフォルトの値を「F」に決めましたが、他のレコードで使われているAやBにしても構いません。nullのレコードは評価なしとしたいため、新しく「F」を設定します。

※サンプルコード

select 氏名, 年度, coalesce(評価, 'F') as 評価 from 成績

結果は以下の通りです。

鈴木剛規の評価がnullからFに置き換えられました。Fの値に倍率を設定すれば、計算の途中でnullに引っかかることなく、ボーナスの付与金額を計算できます。

気を付けたいのはnullがFに置き換わっているのは、coalesceで抽出したこの結果だけです。次のselect文でcoalesceを使わなければ、置き換えられずnullで抽出されます。同じようなselect文を何度も流す場合はcoalesceの書き忘れに注意しましょう。

システムなどに取り込まず手計算をするのであれば、結果をエクセルに出力して置換機能で置き換えることも可能です。ただしそれも抽出するたびに置換を行う必要があり、手間がかかります。

coalesceの入ったselect文をテンプレートのような形で使っていくと、何度も同じ構文のSQLを書かずに済むうえに、データ抽出後のデータ編集作業を減らせます。編集作業の手順が増えるほどミスの可能性も増えるため、できるだけSQLの抽出のみで完結する形で作業しましょう。

coalesceを使う際の注意点

SQLでcoalesceを使う際に注意したいポイントを記載します。

引数の型はすべて同じにする

coalesceは引数1がnullなら引数2を、引数1がnull以外なら引数1を返す、のように引数を複数設定できます。

例としてhometelが自宅電話番号、mobtelが携帯電話番号として以下のような抽出をします。

※サンプルコード

select coalesce (hometel, mobtel, 'なし') as 電話番号 from 名簿

自宅電話番号がnullでなければ自宅電話番号が出ます。

自宅電話番号がnullで、携帯電話番号がnullでなければ、携帯電話番号が出ます。

自宅電話番号も携帯電話番号もnullのときはなしが出ます。

複数列のデータを使って、電話番号を一回で出せる例です。

このとき、hometel、mobtel、なしはすべて文字列である必要があります。たとえ電話番号がハイフンなしの数字のみの番号でも、数値型になっていると頭の0がなくなる、ゼロ落ちを起こす可能性があります。

引数の型がそろっていないとRDBMSによって異なる挙動をするため、エラーやトラブルの原因になりかねません。

isnull関数やifnull関数とは使い方が異なる

coalesceと同じような働きをする関数としてisnullやifnullが挙げられます。nullのデータを置き換えられるという点では同じですが、条件や引数の指定方法が異なるため、それぞれの使い方を覚えましょう。

なお、ここで使用しているisnull関数はSQLServerで使えます。nullかを判定する列名と、nullだった場合に置き換える文字列を引数に持ちます。

※サンプルコード

select isnull(hometel, 'なし') as 自宅電話番号 from 名簿

同じような演算子でis null演算子がありますが、これはwhere句の条件に指定し、nullのデータを抽出するために使うものです。混同しやすいため注意しましょう。

なお、MySQLの場合、ifnull関数が使えます。ifnull関数の構文もisnull関数と同様です。

※サンプルコード

select ifnull(hometel, 'なし') as 自宅電話番号 from 名簿

のような形で記載します。

NVL関数との関連性

先に紹介したisnull関数、ifnull関数の他にもNVL関数と呼ばれるものがあります。それぞれの関連性をご紹介します。

NVL関数とは

NVLとはNull Value Logicの略で、使い方はisnull関数やifnull関数同様に、列名と文字列を指定し、指定した列名がnullのときには文字列を表示します。

ただしNVL関数はOracleでしか使えないため、他のDBMSでも同じSQLを使い回ししたい場合は注意が必要です。Oracle以外のDBMSでNVL関数を使うと構文エラーとなり、結果を取得することができません。

coalesceはどのデータベースでも使える

Oracleでしか使えないNVL関数とは異なり、coalesceはほとんどのDBMSで使える共通の関数です。一時的にデータを抽出するだけならば、NVLでもcoalesceでもそれほど差はありません。しかし異なるDBMSの間で同じSQLを使うケースが想定されるときはcoalesceにしておくと便利になります。

自社のシステムによって使っているRBMSが異なるが、メンテナンスは一人が担当する場合などはRBMSによってSQLを書きわける必要がなくなるため、作業効率も上がります。

coalesceを使ってnullをしっかり管理しよう

値が入っていないnullは、データを取り扱ううえでは嫌われることの多い状態です。抽出データを閲覧するだけならまだしも、加工したり集計したりすることが前提の、統計や分析の分野では検討を複雑にしてしまう要因となります。デフォルト値や初期値とみなせるものの判断が容易にできるなら、nullは置き換えて抽出しておく方が無難です。

システムやデータベースの設計思想としてnullを許容しており、nullがありそうな列を抽出する際は、coalesceで抽出にひと手間かけると後続の処理がスムーズに進みます。