SQLで困りがちなnullをうまく避けられるcoalesceを使いこなす
今回はnullと共にcoalesceをサンプルのデータやSQLを使ってご紹介します。
![SQLで困りがちなnullをうまく避けられるcoalesceを使いこなす](https://x-tech.pasona.co.jp/media/img/9515_mv.png)
今回はnullと共にcoalesceをサンプルのデータやSQLを使ってご紹介します。
スキルアップ
2022/12/26 UP
- プログラミング
- インフラエンジニア
- 開発
データベースからデータを抽出した際、抽出結果にnullが出てくるのを防ぐために使われるのがcoalesceです。
nullの扱いは難しくありませんが、知識がないと値が表示されないなどのエラーの原因となります。今回はnullと共にcoalesceをサンプルのデータやSQLを使ってご紹介します。
![エンジニアの「広がる」キャリアパス](/media/img/bnrToTop_top.jpg)
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を書いてみましょう。
社員番号 | 氏名 | 年度 | 評価 |
---|---|---|---|
4040224 | 田中重郎 | 2022 | A |
4090784 | 山田和美 | 2022 | B |
5020193 | 鈴木剛規 | 2022 | null |
評価列の値にしたがって、一定の倍率のボーナスを付与する計算をしようとしています。今の状態で「評価」列の値を取得すると、鈴木剛規はnullのため値が存在せず、一定の倍率を設定することができません。そのためnullを何かに置き換える必要があります。
今回は評価列がnullの場合、一律で「F」に置き換えることとします。デフォルトの値を「F」に決めましたが、他のレコードで使われているAやBにしても構いません。nullのレコードは評価なしとしたいため、新しく「F」を設定します。
※サンプルコード
select 氏名, 年度, coalesce(評価, 'F') as 評価 from 成績
結果は以下の通りです。
社員番号 | 氏名 | 年度 | 評価 |
---|---|---|---|
4040224 | 田中重郎 | 2022 | A |
4090784 | 山田和美 | 2022 | B |
5020193 | 鈴木剛規 | 2022 | F |
鈴木剛規の評価が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で抽出にひと手間かけると後続の処理がスムーズに進みます。
![エンジニアの「広がる」キャリアパス](/media/img/bnrToTop_btm.jpg)