sqliteでnullを計算式から除外あるいは数値としての0にしたいのですが・・・

Question

いつもありがとうございます。nullの取扱いに手こずっております。

いま、在庫受払台帳の集計のようなものをイメージし、DBには繰越残,入庫,出庫があり（違和感ありますがとりあえず繰越残は所与）、selectしたときに在庫（繰越＋入庫ー出庫）を計算して表示するということするものとします。

問題はnullが混在すると結果はnullになってしまうことです。そこで、回避すべく、nullの場合は計算式に入れないようにcase式で対応しました。

本来はnullだったら数値としての0、というふうにしたいのですが見た目は0になっても文字列としての0という解釈しかしれくれないのか、うまくいかなかったために在庫を計算するタイミングでcase式を入れました。

ほかにもっとスマートな書き方があればご教示のほど、よろしくお願い致します。

sqlite> .schema test
CREATE TABLE test (s1 integer, s2 integer, s3 integer);

// サンプルデータ投入後
sqlite> select * from test;
s1        s2          s3      
--------  ----------  --------
10        10          5       
          10          5       
          10                  
10                            
10                    5       
10        10   


sqlite> select s1, s2, s3, 
   ...> case when s1 is null and s2 is not null and s3 is not null then s2-s3 
   ...> when s1 is null and s2 is not null and s3 is null then s2 
   ...> when s1 is not null and s2 is null and s3 is null then s1 
   ...> when s1 is not null and s2 is null and s3 is not null then s1-s3 
   ...> when s1 is not null and s2 is not null and s3 is null then s1+s2 
   ...> else s1+s2-s3 end 
   ...> from test;

// 結果
10|10| 5|15
  |10| 5| 5
  |10|  |10
10|  |  |10
10|  | 5| 5
10|10|  |20

sqlite3 --version 3.6.20

Answer 1

sqlliteならifnullが使えます。

ifnull(カラム名, 0)

select ifnull(s1,0), ifnull(s2,0), ifnull(s3,0) from test;

Comments

[true]

さっっそくのご回答ありがとうございます！たしかに0になるのですが、s1+s2-s3の結果がnullになってしまいます。

select ifnull(s1,0), ifnull(s2,0), ifnull(s3,0), s1+s2-s3 from test;
10|10|5|15
0|10|5|
0|10|0|
10|0|0|
10|0|5|
10|10|0|

[nanndemoiikara]

select ifnull(s1,0), ifnull(s2,0), ifnull(s3,0), ifnull(s1,0)+ifnull(s2,0)-ifnull(s3,0) from test;

[true]

ありがとうございます！できました。すみません試行錯誤力が足りてませんでした。

[nanndemoiikara]

ifnullの動作は、
第一引数がnullの場合第二引数の値を
第二引数もnullなら第三引数を
.
.
.
といった具合ですので
nullで計算を行わない用にnullが入る値で計算を行うときはifnullでデフォルト値を指定してあげましょう。
また、ifnullはMySQLやたしかSQL Serverでも使えたはずですので覚えておくと便利です。

select
ifnull(s1,0),
ifnull(s2,0),
ifnull(s3,0),
ifnull(s1,0) + ifnull(s2,0) - ifnull(s3,0)
from
test
;

[nanndemoiikara]

解決したようで何よりです。

[true]

create table test (s1 integer default 0, s2 integer default 0・・・のようにしておけばたしかに楽になりますね。1を聞いて3ぐらい得しました。ありがとうございました！

[kutsulog]

>> たしかSQL Serverでも使えたはずですので～
SQL Serverの場合はISNULLまたはCOALESCEですね
ISNULLは引数が2つまで
COALESCEは引数を2つ以上とれます

[nanndemoiikara]

> >> たしかSQL Serverでも使えたはずですので～
> SQL Serverの場合はISNULLまたはCOALESCEですね
> ISNULLは引数が2つまで
> COALESCEは引数を2つ以上とれます

失礼しました！
IFNULLも引数は2つまでです。
COALESCEが複数引数で指定できます。

そしてMySQLでもsqlliteでもSQL Serverでも使えるのが
COALESCEですね。
ご指摘頂きありがとうございます。

勉強になります。

Answer 2

ifnull関数を使用できませんか？

select ifnull(c1,0), ifnull(c2,0).....

Comments

[true]

ご回答ありがとうざいます！ですがs1+s2-s3の結果がnullになってしまうのです。ifnull関数は恥ずかしながら知らなかったのでもっと関数について調べてみたいと思います。