SQLの便利な関数（NULLIF、IIF）を使ってみよう！

2024.10.30 株式会社システムインテグレータ

今回の初心者訓練所#20は、前回に引き続きNULL値に関連したNULLIF関数と、CASE句と同様の使い方ができるIIF関数について説明していきます。どちらの関数もSQL文をシンプルに記入することが出来ますので、是非、覚えてください。いつもの「SQLite」を使用して進めていきますので、「SQLite」をインストールしてから読み進めてください。

NULLIF関数

NULLIF関数は、指定した2つの引数の値を比較して、値が等しければNULLを返し違う場合は初めに指定した引数の値を返します。データ内の特定の値を条件に従って除外する場合などに便利な関数です。
構文は以下となります。

NULLIF ( 項目X , 項目Y )

初めに、引数に直接値を入力して確認してみましょう。

select NULLIF ( 'A' , 'A' );

select NULLIF ( 'A' , 'B' );

blog20-01

最初のSELECT文は引数が同じ値なので、NULLが返され、２番目のSELECT文は引数の値が違うため、初めの引数の値「A」が返されていることが確認できます。

それでは、実際のデータを利用して確認するために、確認用の商品テーブル（tbl_item）を作成してデータを挿入してみましょう。

create table tbl_item ( item_code integer , item_name text , unit_price integer );

insert into tbl_item values
('1','BANANA','0'),
('2','APPLE','100'),
('3','ORANGE','200'),
('4','GRAPE','200'),
('5','PEAR','300'),
('6','PERSIMMON','400'),
('7','STRAWBERRY','100'),
('8','MELON','100');

blog20-02

出力フォーマットを調整して、SELECT文で登録内容を確認してみましょう。

.headers on　（ヘッダ情報を出力）
.mode column　（カラム毎に左揃え出力）

select * from tbl_item;

blog20-03
insert文で作成した8件のデータを確認できます。

それでは、作成したデータを使用して商品単価（unit_price）の平均を求めてみましょう。また、NULLIF関数を使用して商品単価がゼロのデータは対象外とした値も合わせて表示してみます。
以下のSELECT文で確認してみましょう。

select avg( unit_price ) as all_avg
, avg ( NULLIF( unit_price , 0)) as zero_less
from tbl_item;

all_avgは、（0+100+200+200+300+400+100+100）÷ 8 = 175.0
zero_lessは、（100+200+200+300+400+100+100）÷ 7 = 200.0
となり、NULLIF関数を使用すると、先頭のunit_price = 0のデータを対象外にして平均値を計算していることが確認できます。
この様に特定の値を対象外にしたい場合等に、NULLIF関数を使用すると簡単に集計することができます。AVG関数はNULL値は集計対象外となるため、上記のように利用することができます。

IIF関数

IIF関数は、簡単な条件分岐を行える関数になります。初めの引数に条件式Xを指定して、条件式が真ならば項目Yを、偽ならば項目Zを返します。

構文は以下となります。

IIF ( 条件式X , 項目Y , 項目Z )

参考までに、上記の構文をCASE句で記述すると以下となります。

CASE WHEN 条件式X THEN 項目Y ELSE 項目Z END

それでは、先程作成したテーブル「tbl_item」を使用して確認してみましょう。
IIF関数を使用して、商品単価（unit_price）が300以上の場合は「HIGH」を、それ以外の場合は「LOW」表示するSELECT文を試してみましょう。

select item_code,item_name,unit_price,
IIF( unit_price >= 300 , 'HIGH' , 'LOW' ) as price
from tbl_item ;

blog20-05

商品単価が判定されて表示されていることが確認できます。

ちなみに、CASE句でも確認してみましょう。

select item_code,item_name,unit_price,
CASE when unit_price >= 300 then 'HIGH' else 'LOW' end as price
from tbl_item ;

blog20-06

IIF関数と同様の結果になることが確認できます。

説明は以上となります。

あとがき

「初心者訓練所」の第20弾では、SQL文をシンプルに記述できる、覚えておくと便利な２つの関数を説明してきました。説明した以外の利用方法も沢山ありますので、実際に色々試してみて使い方をマスターしてください。この記事が、皆さんの日常的なデータベース操作の効率化に繋がれば幸いです。

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司