今回はSQLでNULLを0や空文字に変換する方法を説明します。
プログラムでデータベース上でデータ取得のクエリを実行させ、実行結果を受け取る処理を書く場合、クエリが返すNULLをそのまま使いたくないというのは、まあまあ良くあることです。
なので、データベースを使用するプログラムを開発する基本的な知識としてSQLでNULLを空文字や0に変換する方法はきちんと知っておく必要があります。
今回はNULLが発生するクエリを例に挙げ、そこからクエリを改修する形で説明していきます。
データ取得時にNULLが発生するクエリ
データ取得時にNULLを取得するクエリは2種類存在します。
1つ目は、データにNULLが入っているテーブルからデータを取得する場合、2つ目はJOINによる外部結合の結果、NULLの列が発生してしまう場合です。
テーブルのデータにNULLが入っているのはよくあることで、テーブルの列にNULLを許容するかどうかを指定して、NULLを許容した場合は、テーブルの列のデータにNULLが初期値で入ります。
基本的にすべての列にNULLを許容しないというケースは少ないので、テーブルに入っているデータにはNULLのデータが入っていることの方が多いと思っておいた方がいいでしょう。
NULLを取得するクエリについて
今回はJOINでテーブルを結合した結果、NULLが発生した場合にNULLを0や空文字に変換することを考えてみます。
例えば、前回の説明で用いた以下のクエリを考えてみます。
SELECT sales.date, item.item_code, sales.seq_no, item.price * sales.amount as sales_price
FROM sales
LEFT JOIN item
ON item.item_code = sales.item_codeこれを実行すると、以下の結果になります。
NULLを0や空文字に変換する場合はCOALESCEを使用します。
COALESCE（＜NULLを変換したい列名＞,<NULLを変換する値>）
という書き方で、NULLを変換することが出来ます。
例えば、以下のようにクエリを書き換えてみます。
SELECT sales.date, COALESCE(item.item_code, '') AS item_code, sales.seq_no,
COALESCE(item.price, 0) * sales.amount as sales_price
FROM sales
LEFT JOIN item
ON item.item_code = sales.item_codeこのクエリでは、itemテーブルを外部結合した際にitem_codeがNULLの場合は空文字に、priceがNULLの場合は0に変換するという処理を行っています。
このクエリの実行結果は以下のようになります。
item_code欄のNULLが消えて空文字になったこと、sales_price欄のNULLが消えて0になったことが確認できます。
このように、クエリの結果のNULLを変換したい場合は、COALESCEを使うとNULLを空文字や0に変換して、クエリの取得結果にNULLを混入させずに済みます。
NULLを変換するCOALESCE以外の書き方
NULLを変換する関数はSQLの種類によってCOALESCE以外にも用意されているものがあります。
SQLServer、MySQL、Oracleでは以下のようにNULLを変換する関数が用意されています。
| SQLServer | ISNULL |
| MySQL | IFNULL |
| Oracle | NVL |
構文はCOALESCEと同じで、第一引数にNULLを変換したい列名、第二引数にNULLを変換する値を設定します。
基本的にCOALESCEを使うとどのSQLでもエラーにならずに変換してくれますが、開発のプロジェクトによってはISNULLやIFNULLなどを使う方が主流になっているプログラムも見かけます。
なので、ただCOALESCEを知っておけばいいというわけではなく、メンテナンスするプログラムや新規で開発するプログラムのSQLの書き方のルールに従って、柔軟に使い分けることが重要になります。
まとめ
今回は以下の点について説明しました。
- NULLを空文字や0に変換したい場合はCOALESCEを使う
- NULLを空文字や0に変換する関数はCOALESCE以外にもあり、開発するプログラムによって使い分けることが重要
NULLを空文字や0に変換する機会は実際の開発の場では結構あるかと思います。
プログラム側でNULLを制御する方法もあるのですが、SQLによりデータを取得した時にNULLの混入を防ぎたい場合は、今回の内容を理解し、きちんと対処が出来るようにしておきたいものです。
今回の記事はここまでとなります。
また次の記事でお会いしましょう。
