SQLのクエリで NULL を0として出力する - SQL
SQLのクエリで NULL を0として出力する方法を紹介します。
概要
SQLのクエリでNULLの値を0として表現したいことがあります。NULLの値を0として出力するには isnull()関数を利用します。
書式
下記の書式となります。
ISNULL(NULLか確認する式, NULLであった場合の返却式)
式の部分には値を指定することもできます。
記述例
value1の値がNULLであった場合に0を返します。
ISNULL(value1, 0)
例
数値でソートをする場合を例にします。NULLの値は最も小さい値として扱われるため、下記のSQLを実行した場合、b.count - a.count の数が小さいものからソートされますが、b.count, a.count どちらかがNULLである場合、 b.count - a.count はNULLとなり、最も小さい数として扱われてしまいます。
select a.id, a.count, b.count, (b.count-a.count) from
(select id, count from countTable where date='2018/2/1') as a
,(select id, count from countTable where date='2018/2/2') as b
WHERE a.id=b.id
order by b.count - a.count ;
countTable
id | count | date |
1 | 10 | 2018/2/1 |
2 | 5 | 2018/2/1 |
3 | NULL | 2018/2/1 |
4 | 3 | 2018/2/1 |
1 | 20 | 2018/2/2 |
2 | 1 | 2018/2/2 |
3 | 8 | 2018/2/2 |
4 | 6 | 2018/2/2 |
上記のテーブルの場合、先のSQLの実行結果は下記となります。
id | a.count | b.count | b.count - a.count |
3 | NULL | 8 | NULL |
2 | 5 | 1 | -4 |
4 | 3 | 6 | 3 |
1 | 10 | 20 | 10 |
実際は下図の動作を期待したいのですが、下記の結果にはなりません。
id | a.count | b.count | b.count - a.count |
2 | 5 | 1 | -4 |
4 | 3 | 6 | 3 |
3 | NULL | 8 | 8 |
1 | 10 | 20 | 10 |
対策
ISNULL()関数を利用してNULLを0として扱います。先のSQLを下記に変更します。下記のSQLの場合、a.count, b.count の値がNULLの場合は0として扱うため、計算結果はNULLにならず、期待した結果が得られます。
select a.id, a.count, b.count, isnull(b.count,0) - isnull(a.count,0) from
(select id, count from countTable where date='2018/2/1') as a
,(select id, count from countTable where date='2018/2/2') as b
WHERE a.id=b.id
order by isnull(b.count,0) - isnull(a.count,0);
著者
iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。